Chapter 22 · advanced · 9 min
Subqueries & CTEs
Sometimes the answer to a question depends on another question's answer. "Drivers rated above average" needs the average first. A subquery is a query nested inside another; a CTE (WITH ... AS) is the same idea with a name, so complex logic reads top-to-bottom.
We'll analyse driver ratings.
The dataset
A drivers table: name, city, and average rating out of 5.
Schema
| id | INT |
| name | TEXT |
| city | TEXT |
| rating | NUMERIC |
Example data
A scalar subquery
A subquery that returns a single value can stand in wherever a value is expected. Here the inner SELECT AVG(rating) FROM drivers computes one number, and the outer query compares each driver against it. The parentheses are required.
Before you run it — Yusof has no rating recorded. Roughly how many of the 6 rated drivers do you expect above average?
A subquery that returns a list
A subquery can also return a column of values, used with IN. This finds drivers in any city that has a driver rated above 4.8; the inner query produces the set of such cities, the outer query filters to them.
Before you run it — only Ahmad (4.9) rates above 4.8. Which city qualifies, and who else in that city comes along?
EXISTS: does a matching row exist at all?
EXISTS checks whether a subquery returns any row; it doesn't care what columns you select inside it (writing SELECT 1 is a common convention). This example is subtly different from the IN version above: the inner query references d.city from the outer row, so it re-runs, in effect, once per outer row. That's called a correlated subquery, versus the earlier IN/FROM subqueries, which compute their result once, independently of the outer query.
Before you run it — should this return the exact same rows as the IN version above?
The NOT IN + NULL trap
Yusof's rating hasn't been recorded yet; it's NULL. Watch what happens when a NOT IN list is built from a column that contains a NULL: SQL can't prove a value is "not equal to" an unknown, so the entire condition quietly becomes unknown too, the exact same NULL trap from earlier in the tutorial, just harder to spot inside a subquery.
Before you run it — Penang's ratings include Yusof's NULL. How many rows do you think this query returns?
NOT EXISTS doesn't have the NULL trap
The same logic, written instead with a correlated NOT EXISTS, which only asks "does a matching row exist" rather than comparing directly against a value. When in doubt, prefer NOT EXISTS over NOT IN for exclusion logic.
Before you run it — does this NOT EXISTS version avoid the 0-rows trap from the NOT IN version above?
A subquery in FROM
You can query the result of a query. Wrap a query in parentheses, give it an alias, and treat it like a table. Here we first compute each city's average, then filter those averages, a two-step calculation in one statement.
Before you run it — Kuala Lumpur has 4.9 and 4.2. Does its average clear 4.3?
The same thing, readable: a CTE
Nested subqueries get hard to read fast. A CTE (Common Table Expression) with WITH name AS (...) lets you name an intermediate result and reference it below, same logic as the previous example, but it reads top-to-bottom like steps in a recipe. Prefer CTEs once a query grows past one level of nesting.
Before you run it — should this return the exact same rows as the FROM-subquery version above?
Choosing between the forms
| Form | Returns | Typical use |
|---|---|---|
| Scalar subquery | one value | compare a row against an aggregate |
IN subquery | a list of values | "is this in the set of..." |
EXISTS | true/false per outer row | "does a matching row exist", often faster than IN on large tables |
Subquery in FROM | a whole result set | a two-step calculation |
CTE (WITH) | same as a FROM-subquery, named | anything nested more than one level, always prefer this for readability |
If you've used Excel or Google Sheets: a scalar subquery is the same idea as a formula referencing AVERAGE() of a whole column inside a comparison (=B2>AVERAGE(B:B)); there's no clean spreadsheet equivalent of EXISTS or a CTE. Naming an intermediate calculation in a spreadsheet usually means burying it in a helper column instead.
Ready to practice? Grab's above-average drivers question below is the scalar-subquery pattern from the top of this chapter.
Subqueries and CTEs are how you break a hard question into stages. When a problem feels like it needs "the answer to a smaller question first", that smaller question is your subquery. Next: window functions, the concept most likely to separate you from other candidates.