A subquery is a SELECT inside another statement. It lets one query answer a question
that depends on the answer to another, and it goes in four places: the select list, the
WHERE clause, the FROM clause, and — as of MySQL 8 — a
CTE, which is usually the more readable version of the same idea.
In the select list: scalar subqueries
SELECT name, (SELECT COUNT(*) FROM product_size ps WHERE ps.product_id = p.id) AS sizes
FROM product p WHERE p.type = 'PIZZA' ORDER BY p.id LIMIT 3;+-----------------+-------+
| name | sizes |
+-----------------+-------+
| Pepperoni Pizza | 3 |
| Cheese Pizza | 3 |
| Supreme Pizza | 3 |
+-----------------+-------+A subquery here must return one row and one column — more than one row is an
error, and no rows gives NULL. This one is correlated: it mentions p.id from
the outer query, so conceptually it runs once per outer row. A LEFT JOIN with
GROUP BY usually does the same job with one pass; see the note on performance
below.
In WHERE: IN, EXISTS and comparisons
SELECT id, customer_name, total FROM customer_order
WHERE id IN (SELECT order_id FROM order_item WHERE product_name = 'Pepsi')
ORDER BY id;+----+---------------+-------+
| id | customer_name | total |
+----+---------------+-------+
| 1 | Demo Customer | 28.91 |
| 11 | Demo Customer | 58.19 |
| 18 | Demo Customer | 27.02 |
+----+---------------+-------+IN takes a list of values. EXISTS takes a correlated subquery and asks
only whether it produced anything — which is why SELECT 1 is the convention inside
one; the columns are never read.
Comparison operators work too, and this is the neat way to ask "above average" — a question you
cannot write with WHERE total > AVG(total), because WHERE runs before
aggregates exist:
SELECT id, total FROM customer_order
WHERE total > (SELECT AVG(total) FROM customer_order)
ORDER BY total DESC LIMIT 3;ANY and ALL extend that to multi-row subqueries —
> ALL (…) means greater than every value.
The NOT IN trap
This one is worth real attention, because it returns a wrong answer rather than an error. Two
conditions set it up, both of which happen naturally in a real database — a product nobody has
ordered yet, and a NULL in the column the subquery selects (order_item.product_id is
nullable, and goes NULL when a product is deleted):
INSERT INTO product (id, name, description, type, active, display_order,
created_at, updated_at, public_id, deleted)
VALUES (99, 'Garlic Knots', 'On the menu, never ordered', 'PIZZA', TRUE, 99,
'2026-01-01', '2026-01-01', 'aaaaaaaa-0000-4000-8000-000000000099', FALSE);
UPDATE order_item SET product_id = NULL WHERE id = 1;Now ask "which products have never been ordered?" three ways. The answer is one — Garlic Knots:
SELECT
(SELECT COUNT(*) FROM product p WHERE NOT EXISTS (SELECT 1 FROM order_item i WHERE i.product_id = p.id)) AS not_exists,
(SELECT COUNT(*) FROM product p WHERE p.id NOT IN (SELECT i.product_id FROM order_item i)) AS not_in,
(SELECT COUNT(*) FROM product p WHERE p.id NOT IN (SELECT i.product_id FROM order_item i
WHERE i.product_id IS NOT NULL)) AS not_in_guarded;+------------+--------+----------------+
| not_exists | not_in | not_in_guarded |
+------------+--------+----------------+
| 1 | 0 | 1 |
+------------+--------+----------------+The correct answer is 1. NOT IN says 0.
The reason is NULL logic. x NOT IN (1, 2, NULL) expands to
x <> 1 AND x <> 2 AND x <> NULL, and that last comparison is never
true — it is unknown. Unknown ANDed with anything is at best unknown, and
WHERE keeps only true. So a single NULL anywhere in the subquery makes
NOT IN return no rows at all, silently.
Two fixes: add WHERE col IS NOT NULL to the subquery, or — better — use
NOT EXISTS, which is immune because it asks about row existence rather than value
equality. Prefer NOT EXISTS. Plain IN is unaffected;
only the negation is.
In FROM: derived tables
SELECT t.order_type, ROUND(AVG(t.order_total), 2) AS avg_order
FROM (SELECT o.order_type, o.total AS order_total FROM customer_order o WHERE o.status = 'COMPLETED') t
GROUP BY t.order_type ORDER BY t.order_type;+------------+-----------+
| order_type | avg_order |
+------------+-----------+
| CARRYOUT | 19.30 |
| DELIVERY | 32.61 |
+------------+-----------+A subquery in FROM is a table for the duration of the query and
must have an alias — t here. Leaving it off is
"Every derived table must have its own alias", which is one of the more common MySQL
errors.
Derived tables are the standard way to aggregate twice: group once inside, then group or filter
the result. They are also how you work around the alias-not-visible-in-WHERE rule from
SELECT.
Correlated or not, and what it costs
An uncorrelated subquery does not mention the outer query. MySQL can run it once and reuse the result. A correlated one depends on the current outer row, so conceptually it runs per row — which on a large table is the difference between one scan and a million lookups.
MySQL 8's optimizer is good at rewriting these, frequently turning IN subqueries
into semi-joins and materialising derived tables. Do not assume either way:
EXPLAIN tells you what it actually chose.
As a rule of thumb, when a subquery and a join express the same thing, the join is easier for the
optimizer and usually easier to read. The exceptions worth keeping are
EXISTS/NOT EXISTS, which say what they mean and cannot duplicate rows the
way a join to a "many" table can.
When to reach for a CTE instead
Anything more than one level of nesting gets hard to read, because you have to read a query inside-out. A common table expression gives each step a name and lets you read top-down:
WITH completed AS (
SELECT order_type, total FROM customer_order WHERE status = 'COMPLETED'
)
SELECT order_type, ROUND(AVG(total), 2) AS avg_order
FROM completed GROUP BY order_type ORDER BY order_type;Same result as the derived table above, and a CTE can also be referenced more than once, which a derived table cannot.
What to remember
- A scalar subquery must return one row and one column.
NOT INwith a NULL in the subquery returns nothing. UseNOT EXISTS.- Derived tables need an alias.
- Correlated subqueries run per outer row — check with
EXPLAINbefore assuming. - Past one level of nesting, use a CTE.