Subquery vs CTE in SQL: Same Logic, One You Can Check

1. What a subquery is, and the four places it goes

A subquery is a SELECT written inside another statement, wrapped in brackets. What it is for depends on where you put it, and there are only four places.

Everything else on this page is one of those four with a variation. Knowing which of the four you are writing tells you immediately what shape the subquery has to return, and most subquery errors are a shape mismatch: a list where one value was expected, or one column where a table was.

2. A scalar subquery: one value

Before the explanation: you want the orders worth more than the average order. Say why you cannot write WHERE revenue > AVG(revenue).

Because WHERE runs before any aggregation, so at the moment it is evaluated there is no average yet. The subquery solves it by computing the average as a separate, complete query first.

SELECT COUNT(*) AS orders, SUM(units*unit_price) AS revenue
FROM orders
WHERE units*unit_price > (SELECT AVG(units*unit_price) FROM orders);

8 | 6475

Eight of the sixteen orders, worth 6,475 between them. The average itself is 9,890 ÷ 16, which is 618.125, and the eight are the ones above it.

A subquery that returns exactly one value is called scalar, and it can be used anywhere a number could be. That includes the select list, which is how you put a whole-table figure beside every row:

SELECT order_id,
       units*unit_price AS revenue,
       ROUND(100.0*(units*unit_price)
             / (SELECT SUM(units*unit_price) FROM orders), 1) AS pct
FROM orders ORDER BY revenue DESC LIMIT 3;

1010 | 1100 | 11.1
1001 |  880 |  8.9
1015 |  880 |  8.9

The largest order is 11.1% of everything. Check it: 1,100 ÷ 9,890 is 0.1112. The subquery ran once, not once per row, because it does not depend on the row it sits beside. That distinction becomes the whole of section four.

3. IN, and the NOT IN that returns nothing

IN takes a list rather than a value, so the subquery returns one column and as many rows as it likes.

SELECT COUNT(*) FROM orders
WHERE order_id IN (SELECT order_id FROM cancelled);

2

Two of the sixteen orders are on the cancelled list. Now the obvious next step, and this is the one to slow down for.

Before the explanation: the cancelled table holds three rows, 1004, 1009 and one NULL. Predict how many of the sixteen orders come back as not cancelled.

SELECT COUNT(*) FROM orders
WHERE order_id NOT IN (SELECT order_id FROM cancelled);

0

Zero. Not fourteen. Every row disappeared, and nothing errored.

The reason is that x NOT IN (a, b, NULL) expands to x <> a AND x <> b AND x <> NULL, and a comparison with NULL is never true, it is unknown. An AND chain containing an unknown can never come out true, so no row qualifies. The full account of why unknown behaves that way is in NULL in SQL, and this is its most expensive consequence.

Two fixes, both verified on the same data:

-- exclude the NULLs from the list
WHERE order_id NOT IN (SELECT order_id FROM cancelled WHERE order_id IS NOT NULL)
14

-- or use NOT EXISTS, which is immune by construction
WHERE NOT EXISTS (SELECT 1 FROM cancelled c WHERE c.order_id = o.order_id)
14

Prefer NOT EXISTS. It cannot be broken by a null appearing in the list next month, it usually plans at least as well, and it reads as what you mean: there is no matching row. Keep IN for the positive case, where a null in the list is harmless.

4. Correlated subqueries: one run per row

A subquery that refers to a column from the outer query cannot be computed once, because its answer is different for every outer row. That is a correlated subquery.

The question: which orders beat the average for their own region? Here are the region averages first, so there is something to check against.

East  | 4 orders | 3040 | 760.00
North | 5 orders | 2495 | 499.00
South | 4 orders | 2670 | 667.50
West  | 3 orders | 1685 | 561.67

SELECT o.order_id, o.region, o.units*o.unit_price AS revenue
FROM orders o
WHERE o.units*o.unit_price > (
  SELECT AVG(i.units*i.unit_price)
  FROM orders i
  WHERE i.region = o.region)
ORDER BY o.region, revenue DESC;

1010 | East  | 1100
1001 | North |  880
1014 | North |  510
1015 | South |  880
1002 | South |  850
1012 | West  |  765

Six orders. Check one: North's average is 499, and North's orders are 880, 510, 440, 425 and 240, so exactly two clear it. The inner query ran sixteen times, once per outer row, each time filtered to that row's region.

Two things follow. It reads well, because the WHERE i.region = o.region line says out loud what "their own region" means. And it is the slowest shape on this page, because it is a loop: on sixteen rows nobody notices, on sixteen million it is the difference between a second and a coffee break. Section eight has the version that does the same job in one pass.

5. The derived table, and where it stops being readable

A subquery in FROM returns a whole table, which the outer query then treats as if it were one. It has to be given an alias, and it is the shape that lets you filter on an aggregate you just computed.

SELECT region, revenue FROM (
  SELECT region, SUM(units*unit_price) AS revenue
  FROM orders GROUP BY 1
) t
WHERE revenue > 2500
ORDER BY revenue DESC;

East  | 3040
South | 2670

This works and it is fine at one level deep. The trouble starts at two, because the reading order inverts: the query executes from the inside out and is written from the outside in, so you scroll down to find the beginning. At three levels, with a join in the middle, nobody reads it at all, including whoever wrote it.

There is also a practical limitation worth knowing before it bites. A derived table can only be used once. If two parts of your query need the same intermediate result, you write it twice, and then somebody edits one copy.

6. The CTE: the same query with a name

A common table expression is the same subquery moved to the top and given a name with WITH. Here is the derived table from section five, unchanged in meaning:

WITH by_region AS (
  SELECT region, SUM(units*unit_price) AS revenue
  FROM orders GROUP BY 1
)
SELECT region, revenue
FROM by_region
WHERE revenue > 2500
ORDER BY revenue DESC;

East  | 3040
South | 2670

Identical output. What changed is that the steps are now in the order they happen, the intermediate result has a name a person chose, and the final SELECT is short enough to read in one go.

They stack, separated by commas, and each one can use the ones above it:

WITH big AS (
  SELECT * FROM orders WHERE units*unit_price > 600
), by_rep AS (
  SELECT rep, COUNT(*) AS orders, SUM(units*unit_price) AS revenue
  FROM big GROUP BY 1
)
SELECT rep, orders, revenue FROM by_rep ORDER BY revenue DESC;

Priya Shah | 3 | 2440
Owen Park  | 3 | 2390
Dana Reyes | 1 |  880
Sam Okafor | 1 |  765

Eight large orders, spread four ways. Reading it top to bottom describes the analysis in the order somebody would explain it out loud: first take the large orders, then group them by rep, then sort. A nested version of the same thing says the last step first.

A named step can also be used more than once, which the derived table could not. The CTE guide goes further into the syntax and into building a query in checkable stages; this page is about when to reach for one instead of a subquery.

7. Counting every step

This is the practical reason to prefer CTEs, and it is worth more than the readability.

When a nested query returns the wrong answer, you have one number, the final one, and no way to tell which layer went wrong. With named steps you can point SELECT COUNT(*) at any of them by changing the last line:

WITH big AS (
  SELECT * FROM orders WHERE units*unit_price > 600
), by_rep AS (
  SELECT rep, COUNT(*) AS orders, SUM(units*unit_price) AS revenue
  FROM big GROUP BY 1
)
SELECT COUNT(*) FROM big;      -- 8
-- SELECT COUNT(*) FROM by_rep;   -- 4

Sixteen orders in, eight survive the filter, four rows come out of the grouping. Write those three numbers down before you run the real query, then compare. A filter that was meant to remove a handful and removed most of the table announces itself immediately, and a grouping that produced more rows than you have categories means a join multiplied something.

Say out loud what the equivalent debugging looks like on a three-deep nested query. You comment out the outer layers one at a time, editing the query to inspect it, which means the thing you are testing is no longer the thing that was wrong. Named steps let you look without changing anything above the last line.

8. When a window function beats both

The correlated subquery from section four asked: is this order above its region's average? Here is the same question with no subquery at all.

SELECT order_id, region, revenue FROM (
  SELECT order_id, region,
         units*unit_price AS revenue,
         AVG(units*unit_price) OVER (PARTITION BY region) AS region_avg
  FROM orders)
WHERE revenue > region_avg
ORDER BY region, revenue DESC;

1010 | East  | 1100
1001 | North |  880
1014 | North |  510
1015 | South |  880
1002 | South |  850
1012 | West  |  765

The same six orders. AVG(...) OVER (PARTITION BY region) computes each region's average once and attaches it to every row of that region, in a single pass, instead of running a separate query per row.

So the rule of thumb: whenever a correlated subquery is computing an aggregate over a group the outer row belongs to, a window function does it faster and usually more clearly. Window functions covers the rest of what OVER can do. Correlated subqueries keep their place for existence checks and for logic a window cannot express.

Picture the slowest query you are responsible for. Is there a subquery inside it that mentions a column from the outer query? That one line is where the time is going.

Edge cases that break a subquery

Six worth knowing.

A scalar subquery that returns more than one row. Fine until the data changes, then the query errors. If you are relying on it returning one row, say so with LIMIT 1 and an ORDER BY, or aggregate it.

A scalar subquery that returns no rows. It gives NULL, not an error, and NULL in a comparison makes the whole condition unknown, so rows quietly disappear rather than a message appearing.

NOT IN against anything nullable. Section three. Assume every column is nullable unless the schema says otherwise, and reach for NOT EXISTS.

A correlated subquery on a large table. It is a loop. If a query got slow after somebody added a filter that references the outer table, this is why.

The same CTE used twice. Some engines recompute it each time it is referenced and some materialise it once, and which one yours does affects both speed and, if the CTE contains anything non-deterministic, the answer. Check your engine's behaviour before relying on it.

An alias that shadows a table. In a correlated subquery, if the inner and outer tables are not clearly aliased, WHERE region = region compares a column to itself and is always true, so the filter silently does nothing. Always alias both, as o and i above.