Subqueries and CTEs: A Query Inside a Query

← back to the section

Some questions can't be answered in one pass. "Show orders above the average" — you first need that average. "Who hasn't ordered anything yet" — first find out who has orders. The answer depends on another answer, and WHERE takes a condition, not a calculation.

That's what a subquery is for — an ordinary SELECT inside another query. Ahead: the three places it goes, its price, and WITH.

A subquery in WHERE: a list and a single value

A filter compares a column with a value or a list. The trouble: we don't have the value — it has to be fetched from the database.

Case one — a list:

live example

SELECT id, status, total_amount
FROM orders
WHERE id IN (SELECT order_id FROM payments WHERE status = 'CAPTURED')
ORDER BY id;

The inner query returns the ids of paid orders; the outer one keeps only those. One requirement: exactly one column, or there's nothing to compare.

Case two — a single value, straight into the comparison:

live example

SELECT id, customer_id, total_amount
FROM orders
WHERE total_amount > (SELECT AVG(total_amount) FROM orders)
ORDER BY total_amount DESC;

Such a subquery is scalar: one row, one column. Return two and the query fails with "more than one row" — usually a forgotten WHERE.

A correlated subquery: "for every row"

In both examples the inner query didn't depend on the outer one: one average for the whole result, computed once. But questions usually sound different — "how many orders does each customer have" — and the inner query needs the current outer row:

live example

SELECT c.id, c.last_name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS orders_count
FROM customer c
ORDER BY c.id;

The inner query refers to c.id, a column of the outer one, so it can't be computed up front: every row has its own answer. That's a correlated subquery — and its price: nine customers, nine passes over orders.

On nine rows you see nothing; on a hundred thousand it's a hundred thousand runs, and a query instant on the test bench hangs for a minute in production. The optimizer sometimes rewrites this into a join — don't count on it (indexes and speed).

EXISTS and NOT EXISTS: a fact, not a value

Sometimes no value is needed: "does this customer have at least one order" is a yes-or-no question, while COUNT(*) counts every order to the end. EXISTS stops at the first match — hence the customary SELECT 1.

The reverse check is needed more often — rows with no match:

live example

SELECT c.id, c.email
FROM customer c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)
ORDER BY c.id;

Two customers without a single order: cus-07 and cus-08. Written as NOT IN (SELECT customer_id FROM orders), the same question becomes a trap: one NULL in the subquery column and the result is empty — no error, just zero rows. NULL and data types has the full story; the rule: IN with a subquery is fine, NOT IN becomes NOT EXISTS.

A subquery in FROM: the derived table

Sometimes the comparison is not against a column but against a grouping — the sum of an order's items against the amount stored on the order. Grouping makes a table that doesn't exist in the database; that's what a subquery in FROM is for:

live example

SELECT o.id, o.total_amount, s.items_total
FROM orders o
LEFT JOIN (SELECT order_id, SUM(quantity * unit_price) AS items_total
           FROM order_items GROUP BY order_id) s ON s.order_id = o.id
WHERE s.items_total IS NULL OR s.items_total <> o.total_amount
ORDER BY o.id;

The subquery in parentheses is a derived table: it lives only inside this query, and a name (here s) is mandatory. Three orders turn up: ord-21, ord-22 and ord-23, recorded at 990, 1200 and 1500 rubles with no items at all. The LEFT JOIN is no accident — a plain join would drop exactly those orders.

WITH: the same query, read top to bottom

The query above reads from the middle outwards: find the parentheses, work out what's inside, come back out. One step is tolerable; on two or three people stop reading. WITH names the intermediate result and lifts it up:

live example

WITH items AS (
    SELECT order_id, SUM(quantity * unit_price) AS amount
    FROM order_items
    GROUP BY order_id
),
mismatched AS (
    SELECT o.id, o.status, o.total_amount, i.amount AS items_amount
    FROM orders o
    LEFT JOIN items i ON i.order_id = o.id
    WHERE i.amount IS NULL OR i.amount <> o.total_amount
)
SELECT * FROM mismatched ORDER BY id;

A named step is a CTE, a common table expression. It reads top to bottom: items — what each order's items cost; mismatched — orders where that sum doesn't match total_amount; the last line — what to show. A step sees the previous ones, so "counted, filtered, sorted" is written in that order.

Same result as the derived table; what changes is readability — and debugging: put SELECT * FROM items instead of the tail to see the first step.

When a subquery is better off as a JOIN

A correlated subquery in the select list is convenient while there's one field. Add the purchase total and the date of the last order — three passes over orders instead of one. A join does it all at once:

live example

SELECT c.id, c.last_name, COUNT(o.id) AS orders_count
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.last_name
ORDER BY c.id;

The numbers match, zeros for cus-07 and cus-08 included — the LEFT JOIN keeps them. The rule: a subquery that checks (IN, EXISTS) stays; one that pulls columns one at a time becomes a join. It works the other way too: a join on the "many" side duplicates rows, while EXISTS never adds one.

In short

• A subquery answers a question that depends on another answer: the average first, then the comparison.
• In WHERE it gives IN a list (one column) or a comparison a single value.
• A correlated subquery runs for every outer row: 100,000 rows, 100,000 runs.
• EXISTS checks a fact and stops at the first row; NOT IN becomes NOT EXISTS.
• A subquery in FROM is a derived table; a name for it is mandatory.
• WITH changes the reading order, not the result: named steps, top to bottom.

What to read next

• JOIN: pulling data from several tables — a join next to a subquery.
• Aggregates: COUNT, GROUP BY and HAVING — what sits inside a subquery.
• NULL and data types — the full NOT IN trap.
• Why a query is slow: indexes in plain words — the cost of extra passes.

What to solve

Queries are written in the marketplace sandbox, without leaving the article.