MENU


Subqueries and Common Table Expressions

A subquery is a SELECT in parentheses inside another statement (SELECT, INSERT, UPDATE, DELETE, SET or DO), letting one query use another's answer: "above the average price", "customers with no reviews". Subqueries differ in the shape they return (a scalar, a column, a row or a table) and in whether they refer to the outer query. A common table expression (CTE) names a subquery up front with WITH, and a recursive one walks trees. The examples run on a freshly loaded bookshop database.

3.6.1 Scalar Subqueries

A scalar subquery returns one value and can stand wherever a literal could: in the select list, a comparison, ORDER BY or SET @avg = (...). WHERE price > AVG(price) is illegal, since an aggregate cannot filter single rows, so the average needs a query of its own:

SELECT sku, price, ROUND(price - (SELECT AVG(price) FROM products), 2) AS vs_avg

FROM products

WHERE price > (SELECT AVG(price) FROM products)

ORDER BY price DESC LIMIT 2;

SELECT (SELECT id FROM orders WHERE customer_id = 1) AS ana_order;

Listing 3.51: Scalar subqueries in WHERE and in the select list, and one that breaks

+-----------+-------+--------+

| sku | price | vs_avg |

+-----------+-------+--------+

| BK-LAR-01 | 49.00 | 17.01 |

| BK-SQL-01 | 44.50 | 12.51 |

+-----------+-------+--------+

2 rows in set (0.001 sec)

ERROR 1242 (21000): Subquery returns more than 1 row

Output of Listing 3.51 EXPLAIN FORMAT=TREE marks the average "run only once". No row gives NULL, as for Gus, who never ordered. Two rows give error 1242 at run time, so guarantee one row by shape: an ungrouped aggregate, a primary-key lookup, or ORDER BY ... LIMIT 1. Two columns are error 1241.

3.6.2 Column, Row, and Table Subqueries

A column subquery returns one column and feeds IN, ANY and ALL (Section 3.6.3). A row subquery returns one row, compared with a row constructor: (customer_id, status) = (SELECT customer_id, status FROM orders WHERE id = 1) finds Ana's shipped orders 1 and 4. A table subquery returns rows and columns, and a tuple IN tests whole rows:

SELECT id, product_id, rating FROM reviews

WHERE product_id IN (1, 2) AND (product_id, rating) IN

(SELECT product_id, MAX(rating) FROM reviews GROUP BY product_id);

SELECT sku FROM products

WHERE id IN (SELECT product_id FROM order_items ORDER BY quantity DESC LIMIT 2);

Listing 3.52: Each product's best review with a tuple IN, and a refused LIMIT

+----+------------+--------+

| id | product_id | rating |

+----+------------+--------+

| 1 | 1 | 5 |

| 2 | 2 | 4 |

+----+------------+--------+

2 rows in set (0.001 sec)

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME

subquery'

Output of Listing 3.52 This greatest-per-group query drops Elif's 4 for product 1 (review 6) and would keep ties. Rows compare left to right, and (1, 2) = (1, NULL) is unknown. For the refused LIMIT, wrap the query in a derived table, IN (SELECT product_id FROM (SELECT ... LIMIT 2) AS t), which returns the mug and the sticker pack. Since 8.0.19, IN (TABLE wishlist) also works.

3.6.3 IN, ANY, ALL, and EXISTS

x IN (...) equals x = ANY (...); x > ANY (or SOME) holds for at least one row and x > ALL for every row; <> ALL is NOT IN. EXISTS only asks whether a row comes back, so its select list is ignored. price > ALL (SELECT price FROM products WHERE category_id = 4) finds the four books dearer than every Design title. The edges hold the surprises:

SELECT (SELECT COUNT(*) FROM products WHERE price > ALL

(SELECT price FROM products WHERE category_id = 9)) AS all_empty,

(SELECT COUNT(*) FROM products WHERE price >

(SELECT MAX(price) FROM products WHERE category_id = 9)) AS max_empty,

3 IN (1, NULL) AS in_n, 3 NOT IN (1, NULL) AS not_in_n, EXISTS (SELECT NULL) AS ex;

Listing 3.53: ALL over an empty set, and IN meeting NULL

+-----------+-----------+------+----------+------+

| all_empty | max_empty | in_n | not_in_n | ex |

+-----------+-----------+------+----------+------+

| 8 | 0 | NULL | NULL | 1 |

+-----------+-----------+------+----------+------+

1 row in set (0.001 sec)

Output of Listing 3.53 Category 9 does not exist: ALL over no rows is true for all 8 products, while MAX of nothing is NULL. The NULLs explain the NOT IN trap of Section 3.5.8: 3 might be the NULL, so IN and NOT IN are both unknown; EXISTS is never unknown. Write "no matching row" as NOT EXISTS, or filter IS NOT NULL inside the NOT IN. MySQL rewrites IN and EXISTS into semijoins and, since 8.0.17, their negations into antijoins (Section 3.12).

3.6.4 Correlated Subqueries

A correlated subquery refers to an outer column, so its answer changes per row. Which products cost more than the average of their own category?

SELECT p.sku, p.category_id, p.price FROM products AS p

WHERE p.price > (SELECT AVG(q.price) FROM products AS q

WHERE q.category_id = p.category_id);

Listing 3.54: Comparing each product with its own category's average

+-----------+-------------+-------+

| sku | category_id | price |

+-----------+-------------+-------+

| BK-SQL-01 | 3 | 44.50 |

| BK-LAR-01 | 2 | 49.00 |

| AC-MUG-01 | 5 | 12.50 |

+-----------+-------------+-------+

3 rows in set (0.001 sec)

Output of Listing 3.54 The mug beats its category's 8.745. Alias both tables: a bare category_id inside would mean q's. Logically the subquery reruns per outer row; EXPLAIN FORMAT=TREE marks it "dependent", probing the index on q.category_id. Most EXISTS tests are correlated too.Without an index on the correlation column, each outer row rescans the inner table. Index it, or build the inner result once: join a grouped derived table (Section 3.6.5), or use AVG(price) OVER (PARTITION BY category_id) (Section 3.8.4). The subquery_to_derived optimizer switch, off by default, makes the first rewrite automatically.

3.6.5 Derived Tables and LATERAL

A derived table is a subquery in FROM, and it needs an alias (error 1248). It fixes the fan-out of Section 3.5.8: aggregate each child alone, then join. A lateral derived table (MySQL 8.0.14+) may also see tables to its left, so it runs per outer row and, unlike a scalar subquery, returns several columns:

SELECT p.sku, s.units, r.reviews

FROM products AS p

LEFT JOIN (SELECT product_id, SUM(quantity) AS units

FROM order_items GROUP BY product_id) AS s ON s.product_id = p.id

LEFT JOIN (SELECT product_id, COUNT(*) AS reviews

FROM reviews GROUP BY product_id) AS r ON r.product_id = p.id

WHERE p.id = 1;

SELECT c.name, latest.id, latest.ordered_at

FROM customers AS c

JOIN LATERAL (SELECT o.id, o.ordered_at FROM orders AS o

WHERE o.customer_id = c.id

ORDER BY o.ordered_at DESC LIMIT 1) AS latest

WHERE c.country = 'MY';

Listing 3.55: Pre-aggregated derived tables, and each customer's latest order with LATERAL

+-----------+-------+---------+

| sku | units | reviews |

+-----------+-------+---------+

| BK-PHP-01 | 3 | 2 |

+-----------+-------+---------+

1 row in set (0.001 sec)

+--------------+----+---------------------+

| name | id | ordered_at |

+--------------+----+---------------------+

| Chen Wei | 8 | 2026-08-20 18:20:00 |

| Farah Rahman | 7 | 2026-07-14 15:55:00 |

+--------------+----+---------------------+

2 rows in set (0.001 sec)

Output of Listing 3.55 Product 1 shows the true 3 units and 2 reviews, not 6 and 4. Without LATERAL, the second query fails with error 1054 on c.id. LEFT JOIN LATERAL (...) ON TRUE keeps customers with no orders, and LIMIT 3 gives top-N per group, which Section 3.8.5 also solves with ROW_NUMBER(). The optimizer merges a simple derived table into the outer query and materializes a grouped one.

3.6.6 Subqueries in UPDATE and DELETE

UPDATE and DELETE accept subqueries in WHERE and SET. Here a clearance discount hits the titles nobody has ordered since June, then two statements try to delete below-average reviews:

UPDATE products SET price = ROUND(price * 0.9, 2)

WHERE id NOT IN (SELECT i.product_id FROM order_items AS i

JOIN orders AS o ON o.id = i.order_id

WHERE o.ordered_at >= '2026-06-01');

DELETE FROM reviews WHERE rating < (SELECT AVG(rating) FROM reviews);

DELETE r FROM reviews AS r

JOIN (SELECT AVG(rating) AS avg_rating FROM reviews) AS a ON r.rating < a.avg_rating;

Listing 3.56: A clearance discount, error 1093, and a multi-table DELETE

Query OK, 2 rows affected (0.008 sec)

Rows matched: 2 Changed: 2 Warnings: 0

ERROR 1093 (HY000): You can't specify target table 'reviews' for update in FROM clause

Query OK, 2 rows affected (0.005 sec)

Output of Listing 3.56 The NOT IN is safe because product_id is NOT NULL; Indexing Deep Dive and Designing Calm Interfaces drop to 26.10 and 30.60. Error 1093 forbids reading the target table in a subquery. Let the server compute the value first instead: join a materialized derived table (the average, 3.857), or nest one, rating < (SELECT a FROM (SELECT AVG(rating) AS a FROM reviews) AS t). Either removes reviews 3 and 7. Multi-table DELETE t FROM ... JOIN and UPDATE a JOIN b SET ... are MySQL extensions. Test the WHERE as a SELECT first.

3.6.7 Common Table Expressions with WITH

A CTE is a named subquery written before the statement that uses it, WITH name [(columns)] AS (SELECT ...); several are separated by commas, and each may read the earlier ones, so a query reads top to bottom instead of inside out. Who brought the most revenue, and what share?

WITH order_totals AS (

SELECT o.id, o.customer_id, SUM(i.quantity * i.unit_price) AS total

FROM orders AS o JOIN order_items AS i ON i.order_id = o.id

WHERE o.status <> 'cancelled' GROUP BY o.id),

customer_totals (customer_id, spent) AS (

SELECT customer_id, SUM(total) FROM order_totals GROUP BY customer_id)

SELECT c.name, t.spent,

ROUND(100 * t.spent / (SELECT SUM(total) FROM order_totals), 1) AS pct

FROM customer_totals AS t JOIN customers AS c ON c.id = t.customer_id

ORDER BY t.spent DESC LIMIT 2;

Listing 3.57: Two chained CTEs, one of them read twice

+------------+--------+------+

| name | spent | pct |

+------------+--------+------+

| Ana Souza | 151.40 | 24.9 |

| Ben Carter | 142.46 | 23.4 |

+------------+--------+------+

2 rows in set (0.002 sec)

Output of Listing 3.57 order_totals is read twice, which a derived table cannot do, and a materialized CTE is built once however often it is read. The list after customer_totals names its columns.CTEs arrived in MySQL 8.0. A WITH may also open an UPDATE or DELETE: WITH low AS (SELECT id FROM reviews WHERE rating <= 2) DELETE FROM reviews WHERE id IN (SELECT id FROM low) deletes review 7, where the same subquery without the CTE raises error 1093. Only one WITH is allowed per query level; WITH a AS (...) WITH b AS (...) is a syntax error.

3.6.8 Recursive CTEs and Hierarchy Queries

In WITH RECURSIVE, the anchor SELECT produces the first rows, and the recursive SELECT after UNION ALL joins the previous round's rows to find the next, until a round finds nothing. Three new categories deepen the tree, and one query walks it from Programming down:

INSERT INTO categories (name, parent_id) VALUES ('Web', 2), ('PHP', 6), ('Laravel', 7);

WITH RECURSIVE tree AS (

SELECT id, name, 0 AS depth, CAST(name AS CHAR(200)) AS path

FROM categories WHERE id = 2

UNION ALL

SELECT c.id, c.name, t.depth + 1, CONCAT(t.path, ' > ', c.name)

FROM categories AS c JOIN tree AS t ON c.parent_id = t.id

)

SELECT id, depth, path FROM tree ORDER BY path;

WITH RECURSIVE n (i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n)

SELECT COUNT(*) FROM n;

Listing 3.58: Walking a subtree, and a recursion with no stop

Query OK, 3 rows affected (0.011 sec)

Records: 3 Duplicates: 0 Warnings: 0

+------+-------+-----------------------------------+

| id | depth | path |

+------+-------+-----------------------------------+

| 2 | 0 | Programming |

| 6 | 1 | Programming > Web |

| 7 | 2 | Programming > Web > PHP |

| 8 | 3 | Programming > Web > PHP > Laravel |

+------+-------+-----------------------------------+

4 rows in set (0.001 sec)

ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing

@@cte_max_recursion_depth to a larger value.

Output of Listing 3.58 Anchor on parent_id IS NULL for the whole tree. Column types come from the anchor alone: uncast, path would be VARCHAR(50), and a long enough path fails with error 1406, "Data too long for column 'path'". The recursive part may not use aggregates, window functions, GROUP BY, ORDER BY or DISTINCT. To walk upward, anchor on Laravel and join c.id = up.parent_id. Joined to products, the CTE answers "how many products sit anywhere under Books": 6.The second query has no stop condition, so cte_max_recursion_depth (default 1000) aborts it. Add WHERE i < 3000 and raise the cap for your session only: SET SESSION cte_max_recursion_depth = 5000. A cycle in the data hits the same error; UNION DISTINCT ends such a walk.