The idea
Every query returns a table. A table of one row and one column is a number. A table with one column is a list of values. A table with several columns is... a table. So anywhere SQL expects a number, a list or a table, you can put a query in parentheses instead.
That is all a subquery is: a query used as a part of another query. The inner query runs, becomes a value, a list or a table, and the outer query carries on as if you had typed that result in by hand.
Figure 1
A subquery runs first and hands back one number
| product_id | name | unit_price |
|---|---|---|
| 156 | Willow Acacia Knife Block | 53.99 |
| 183 | Glen Glass Canvas Print | 189.00 |
| 216 | Tidewater Concrete String Lights | 379.00 |
| avg_price |
|---|
| 270.16 |
| name | unit_price |
|---|---|
| Tidewater Concrete String Lights | 379.00 |
- The question: which products cost more than the average product? The outer query needs a number it does not have yet.
- The inner query in parentheses runs first, on its own, over all 120 products. It returns a table with one row and one column: 270.16.
- Now the outer query sees
WHERE unit_price > 270.16, a plain comparison. It checks each product against that one number. - Only the string lights pass. The inner query ran once, not once per product.
Why it exists
Plenty of questions have a number you do not know yet. "Products priced above average" needs the average. "Premium picks in each category" needs one average per category. "Customer lifetime value" needs revenue per order before it can add orders up per customer.
Without subqueries you would run the first query, copy the answer, and paste it into the second. That breaks the day the data changes and you cannot hand the result to anyone else. A subquery keeps both questions in one statement, so the answer updates itself and the logic is written down.
How it works under the hood
Three places a subquery can sit
As a single value (a scalar subquery). It must return exactly one row and one column. You use it
in WHERE unit_price > (SELECT AVG(unit_price) FROM products) or in the SELECT list. If it returns
two rows, you get an error, which is better than a silent wrong answer.
As a list or a test. IN (SELECT …) checks whether a value is in the returned list. EXISTS (SELECT …) checks whether the inner query returns any row at all. The columns it selects do not
matter, which is why people write SELECT 1.
As a table. A subquery in FROM is treated as a table with an alias. This is how you aggregate
first and then join: FROM (SELECT order_id, SUM(…) AS total FROM order_items GROUP BY order_id) t.
Correlated or not
A subquery that does not mention the outer query is evaluated once, as the first diagram shows. A
correlated subquery refers to the outer row (WHERE o.customer_id = c.customer_id). It is
conceptually evaluated again for each outer row, and each time it uses that row's values. "Premium
picks in every category" is correlated: the average it compares to depends on the product's
category.
Figure 2
EXISTS asks one yes/no question per row and stops at the first match
| order_item_id | order_id | quantity |
|---|---|---|
| 13 | 1004 | 2 |
| 14 | 1005 | 1 |
| 15 | 1005 | 4 |
| return_id | order_item_id | reason |
|---|---|---|
| 1 | 14 | damaged |
| 2 | 42 | wrong_item |
| order_item_id | order_id |
|---|---|
| 14 | 1005 |
- Goal: order lines that were returned. For each line, ask: does a row in
returnsexist whoseorder_item_idequals this line's? - Line 13: nothing in
returnspoints at it. The answer is no, the row is dropped. - Line 14:
returnsrow 1 points at it. One match is enough to answer yes, so the database stops looking and keeps the line. - Line 15 has no return. Unlike a JOIN,
EXISTSnever repeats a line, however many returns it has, because it only answers yes or no.
EXISTS is a yes/no per row, so it never multiplies rows. A JOIN to returns would produce one row
per return. If you only need to know whether a return exists, EXISTS is safer and often clearer.
CTEs: naming the steps
A common table expression is a subquery with a name, written first with WITH. Nothing about
the maths changes. What changes is that you can read the query top to bottom, as a recipe, and test
each step alone.
Figure 3
A CTE chain: each step is a small query you can run on its own
-
order_itemsone row per line -
order_totals AS (…)one row per order -
customer_ltv AS (…)one row per customer -
SELECT … FROM customer_ltvthe answer
- Order 1049 has three lines in
order_items: 378.00, 107.98 and 1,137.00. That is the grain we start from. order_totalssums the lines per order. The three rows fold into one: 1,622.98. The grain is now one row per order.customer_ltvsums the order totals per customer. Because it reads orders, not lines, nothing is counted twice.- The final
SELECTreads the last step like any table. If a number looks wrong, run the CTE above it by itself and check there.
The most useful habit this gives you is pre-aggregating before a join. Shipping fees live on
orders, order lines live on order_items, and joining them directly repeats each fee once per
line. Summing the lines into one row per order first, then joining, keeps the grain at one row per
order. A CTE makes that step visible instead of hiding it inside a long join.
Two more things to know. A CTE exists only for the one statement it belongs to; it is not saved. And a CTE may refer to the CTEs written above it, which is how a chain of steps is built. Recursive CTEs (a CTE that refers to itself) generate rows, for example a calendar with one row per day, so you can show days that have zero orders.
Reading a nested query
Read it inside out. Find the innermost parentheses, run that part alone, and look at what it returns: one number, a list, a table. Then replace it in your head with that result and read the next layer out. This is also how you debug: when a long query gives a strange answer, paste each inner piece into its own editor tab and check its row count against something you trust. Most nested-query bugs are a grain problem hiding in one layer, such as a step that returns one row per order line when the next step assumed one row per order.
Which one to reach for
For one number, use a scalar subquery. For "does a related row exist", use EXISTS (and NOT EXISTS for "does not exist"). For anything with two or more steps, or where you will want to check
an intermediate result, use a CTE.
Common misconceptions
"A subquery is slower, so I should always use a JOIN"
Modern databases rewrite many subqueries into joins on their own, and the choice is mostly about
what reads clearly and what answers the right question. EXISTS is often better than a join because
it cannot create duplicate rows. Optimise after the query is correct and measured to be slow.
"A CTE saves its result like a table"
A CTE is a name for a query, valid for one statement. Nothing is stored, and the next statement cannot see it. If you need the result to stay, you create a table or a view, which is a different tool.
"NOT IN and NOT EXISTS mean the same thing"
They match on every row without NULLs and differ the moment the inner list contains a NULL: x NOT IN (1, NULL) is never true, so the query returns nothing. NOT EXISTS does not have this problem.
When you want "rows with no match", reach for NOT EXISTS or a LEFT JOIN … IS NULL.
Check yourself
One question. Answering it ticks this lesson off on the map; it never locks a level.
Not quite. That is what a JOIN does. EXISTS only answers yes or no for each customer, so a customer is kept or dropped once.Try another answer.
Not quite. That describes a scalar subquery. A correlated EXISTS is evaluated for each customer row, because it refers to c.customer_id.Try another answer.
Right. For each customer the database asks whether any order matches and can stop at the first one. The customer is kept once.
Not quite. A customer with no orders gets a no, so they are left out. A LEFT JOIN would keep them with NULLs.Try another answer.
Lesson done: it is ticked on the map.