A subquery nests one query inside another; a JOIN stitches tables together side by side. They often answer the same question, and the engine frequently rewrites one into the other. Knowing which to reach for is about readability, correctness around NULLs, and matching the shape of the question to the shape of the query.
Almost every intermediate SQL question can be phrased two ways: "run a small query and feed its answer into a bigger one" (a subquery) or "line the tables up and match their rows" (a JOIN). Beginners tend to pick whichever they learned first and force every problem through it. That works until it does not: some queries read far more clearly as a subquery, some are correct only as a JOIN, and a few classic patterns quietly return the wrong answer if you choose badly around NULLs. This guide shows the full subquery family, puts subqueries and JOINs head to head on the same problems, and gives you a decision table you can keep next to your keyboard. If you want the JOIN mechanics in depth first, read the SQL joins guide; here we focus on the choice between the two.
What this guide covers
1. What a subquery is
A subquery is a complete SELECT statement wrapped in parentheses and used inside another statement. The outer (or main) query uses the subquery's result as a value, a list, or a whole table. You can place a subquery in almost every clause: in SELECT to compute an extra column, in FROM to build a temporary table, and in WHERE or HAVING to filter against a computed set. Every example below uses this two-table schema.
-- One department has many employees.
CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
dept_id INT, -- foreign key -> departments.id
salary DECIMAL(9,2)
);The sample data has one department (Design, id 3) with no employees, and one employee (Rae) whose dept_id is NULL. Those two edge rows are exactly what make subqueries and JOINs behave differently later.
| employees.id | name | dept_id | salary |
|---|---|---|---|
| 1 | Ava | 1 | 62000.00 |
| 2 | Ben | 1 | 55000.00 |
| 3 | Cara | 2 | 71000.00 |
| 4 | Rae | NULL | 48000.00 |
Departments are: 1 Engineering, 2 Sales, 3 Design. The official references are worth bookmarking: the PostgreSQL subquery expressions page and the MySQL subquery syntax chapter both spell out the exact rules each engine enforces.
2. Subquery types: scalar, column, row, table
Subqueries are classified by the shape of what they return. Getting the shape right is what makes them legal in a given position.
| Type | Returns | Where it fits |
|---|---|---|
| Scalar | One row, one column (a single value) | Anywhere a value is allowed: SELECT, WHERE, ON |
| Column | Many rows, one column (a list) | With IN, ANY, ALL |
| Row | One row, several columns (a tuple) | Row comparisons like (a, b) = (...) |
| Table | Many rows, many columns | In FROM as a derived table |
Scalar subquery in SELECT and WHERE
A scalar subquery returns exactly one value. Here it computes the company average salary once and compares each employee against it:
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- avg is 59000; returns Ava (62000) and Cara (71000)The same style of scalar subquery works in the SELECT list to add a computed column, for example (SELECT name FROM departments d WHERE d.id = e.dept_id) to fetch a department name per employee. If a scalar subquery ever returns more than one row, the database raises an error, so scalar position demands a guaranteed single value.
Column subquery with IN
A column subquery returns a list you can test membership against:
-- Employees in departments based in the London office.
SELECT name
FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE name = 'Sales');Row and table subqueries are less common day to day but valuable: a row subquery compares several columns at once, and a table subquery becomes a named source in FROM (section 6).
3. Correlated vs non-correlated subqueries
This distinction drives both correctness and speed, so it is worth pinning down. A non-correlated (independent) subquery can run on its own; it does not reference the outer query. The database can evaluate it once and reuse the result.
-- The inner query runs once, standalone.
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);A correlated subquery references a column from the outer query, so conceptually it runs once per outer row. Here the inner query depends on e.dept_id, which changes with every outer row:
-- Employees paid above their OWN department average.
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(x.salary)
FROM employees x
WHERE x.dept_id = e.dept_id -- references the outer row
);| name | salary |
|---|---|
| Ava | 62000.00 |
Only Ava clears her department average (Engineering averages 58500), so she is the single result. The mental model "once per outer row" is what you should carry, even though modern optimizers rarely execute it that literally. A correlated subquery is the natural way to express "compare each row against a group it belongs to" without a separate aggregation step, and it reads almost like the English sentence above.
Quick test: if you can copy the inner query, paste it into a fresh editor tab, and run it with no errors, it is non-correlated. If it complains about an unknown alias from the outer query, it is correlated.
4. The same problem: subquery vs JOIN
Take a concrete question: "list each employee with their department name". You can answer it with a scalar subquery in the SELECT list, or with a JOIN. Both are valid; the results are the same shape.
SELECT
e.name,
(SELECT d.name FROM departments d
WHERE d.id = e.dept_id) AS dept
FROM employees e;SELECT e.name, d.name AS dept
FROM employees e
LEFT JOIN departments d ON d.id = e.dept_id;| name | dept |
|---|---|
| Ava | Engineering |
| Ben | Engineering |
| Cara | Sales |
| Rae | NULL |
Both keep Rae, whose dept_id is NULL, with a NULL department. So which is better? For pulling in one column, the difference is mostly taste. The JOIN pulls ahead the moment you want several columns from the department: the correlated scalar subquery would need one subquery per column, each re-scanning departments, while the JOIN fetches them all in a single match. This is the general rule: when you need columns from the other table, prefer a JOIN. Subqueries shine when you need a computed value or a filter, not a bag of columns.
The reverse case also matters. If you want "employees earning more than the company average" (section 2), the scalar subquery is the clean choice; forcing it into a JOIN would mean cross joining to a one row aggregate, which is more code for no gain. Reach for a JOIN to combine rows, and a subquery to compute or filter. The advanced SQL queries lesson works through more of these trade-offs with runnable examples.
5. EXISTS vs IN vs JOIN for existence checks
A huge share of real queries boil down to "does a related row exist?" There are three common ways to ask, and they are not interchangeable once NULLs enter. The question below: "which departments have at least one employee?"
SELECT d.name
FROM departments d
WHERE EXISTS (
SELECT 1 FROM employees e WHERE e.dept_id = d.id
);SELECT d.name
FROM departments d
WHERE d.id IN (SELECT dept_id FROM employees);SELECT DISTINCT d.name
FROM departments d
JOIN employees e ON e.dept_id = d.id;All three return Engineering and Sales, and correctly drop Design (no employees). Notice the JOIN version needs DISTINCT: because Engineering has two employees, the raw join would list it twice. EXISTS and IN return each department once by construction, which is often why they read better for a pure "has a match" test. EXISTS also short circuits: the engine stops scanning employees the instant it finds the first match, so SELECT 1 is enough, its value is never used.
The trouble starts with the negative question: "which departments have no employees?" The NOT EXISTS and NOT IN versions look equivalent, but they disagree when the subquery column contains a NULL, and ours does (Rae's dept_id).
SELECT d.name
FROM departments d
WHERE NOT EXISTS (
SELECT 1 FROM employees e WHERE e.dept_id = d.id
); -- correctly returns DesignSELECT d.name
FROM departments d
WHERE d.id NOT IN (SELECT dept_id FROM employees);
-- returns NOTHING, because the list contains a NULLThe NOT IN + NULL trap: when the subquery returns a NULL, d.id NOT IN (1, 2, NULL) evaluates to UNKNOWN, never true, so the whole query returns zero rows. It is not a bug in your data, it is three valued logic doing exactly what the standard says. Prefer NOT EXISTS for anti-joins; it treats NULLs correctly and usually optimizes at least as well. This slip is on our SQL cheat sheet of things to double check.
The positive IN is perfectly safe; only NOT IN over a nullable column is dangerous. When in doubt, make the anti-join an EXISTS and move on.
6. Derived tables and CTEs
A subquery in the FROM clause is a derived table: a temporary, unnamed result you query as if it were a real table. It is the tool for "aggregate first, then join or filter". Here we find the top earner per department by first computing each department's maximum salary, then joining back:
SELECT e.name, e.dept_id, e.salary
FROM employees e
JOIN (
SELECT dept_id, MAX(salary) AS top_pay
FROM employees
GROUP BY dept_id
) m ON m.dept_id = e.dept_id AND m.top_pay = e.salary;A derived table must have an alias (m above); an unnamed FROM subquery is a syntax error in most engines. The exact same logic reads more clearly as a common table expression (CTE), introduced with WITH. A CTE is a named subquery declared up front, so complex queries read top to bottom instead of inside out:
WITH dept_max AS (
SELECT dept_id, MAX(salary) AS top_pay
FROM employees
GROUP BY dept_id
)
SELECT e.name, e.dept_id, e.salary
FROM employees e
JOIN dept_max m
ON m.dept_id = e.dept_id AND m.top_pay = e.salary;CTEs and derived tables are usually interchangeable, and the optimizer often treats them identically. The wins with a CTE are human: you can name the step, reference it more than once without repeating the SQL, and (with WITH RECURSIVE) walk hierarchies that a plain subquery cannot. Reach for a CTE the moment a nested subquery gets deep enough that you lose track of which SELECT you are reading. For the broader SELECT toolkit around all of this, the DQL commands reference covers the surrounding clauses.
7. Decision table: which to prefer
When you know what you are trying to do, the choice is usually clear. This table maps the need to the tool. Keep it handy while you write.
| What you need | Prefer a subquery | Prefer a JOIN |
|---|---|---|
| Columns from the other table | No | Yes, one match fetches them all |
| A single computed value to compare against | Yes, scalar subquery | No, awkward |
| "Has at least one match" test | Yes, EXISTS or IN | Works, but needs DISTINCT |
| "Has no match" (anti-join) | Yes, NOT EXISTS | Yes, LEFT JOIN ... IS NULL |
| Compare each row to its group | Yes, correlated subquery | Window function is often better |
| Aggregate first, then combine | Yes, derived table or CTE | Join the derived table |
| Readability on deep logic | Yes, a named CTE | Long join chains get dense |
Notice that the honest answer to "has no match" is a tie: both NOT EXISTS and the LEFT JOIN ... WHERE right.key IS NULL anti-join pattern are correct and fast, so use whichever your team reads more easily. The one row where a subquery is almost always wrong is "I need several columns from the related table", and the one row where a JOIN is almost always clumsy is "I need a single aggregate to compare against".
8. Performance: what the optimizer does
A common myth is that "JOINs are fast and subqueries are slow". On any modern database that is mostly false. The optimizer does not run your SQL literally; it rewrites it into an internal plan and then costs alternatives. In practice a non-correlated IN subquery, a correlated EXISTS, and the equivalent semi join often compile to the same physical plan. The engine is free to turn a subquery into a join and a join into a hash based semi join, whichever it estimates is cheaper.
So what actually moves the needle? The same things that speed up any query: indexes on the join and filter keys, fresh statistics, and avoiding needless row multiplication. The chart below shows the rough relative impact of common levers on a subquery or join heavy query.
The last bar is deliberately small: hand rewriting a subquery as a JOIN rarely changes performance on a well tuned database, because the optimizer already considered both. The exceptions are worth knowing. A correlated subquery in the SELECT list that runs once per output row can be genuinely slow on large result sets, and rewriting it as a JOIN or a window function can help. Older MySQL versions (before 8.0) handled some IN subqueries poorly and benefited from manual rewrites; current versions largely fixed this, as the MySQL documentation notes. The reliable habit is to stop guessing and read the plan.
Write the clearest version first
Choose subquery or JOIN based on readability and correctness, using the decision table above. Correct and clear beats clever.
Measure with EXPLAIN
Run EXPLAIN (or EXPLAIN ANALYZE) to see the real plan and where time goes. Do not optimize on a hunch.
Fix the actual bottleneck
Usually a missing index on a join or filter key, not the subquery versus JOIN choice. The performance tuning guide walks the full playbook.
That is the whole picture. Pick the form that states the question most directly: a JOIN to bring rows together, a subquery to compute or test a value, EXISTS for existence, NOT EXISTS for absence, and a CTE when the logic gets deep. Trust the optimizer for the rest, and verify with EXPLAIN when a query is slow. Every example here runs against the two table schema at the top, so paste them in and watch each form return the same answers. To drill these patterns hands on, work through the SQL intermediate course.