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.
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:
| name | rating |
|---|---|
| Ahmad | 4.9 |
| Chandra | 4.7 |
| Emma | 4.5 |
Drivers rated 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:
| name | city | rating |
|---|---|---|
| Ahmad | Kuala Lumpur | 4.9 |
| Bee Lin | Kuala Lumpur | 4.2 |
Everyone in a 'top-rated' city
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.
Same answer as IN, written as a correlated EXISTS
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: the query below is trying to find drivers whose rating isn't one of Penang's ratings, but it silently returns zero rows, for every driver, including ones nowhere near Penang. 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.
Bugged: 0 rows, because Penang has a NULL rating
NOT EXISTS doesn't have the NULL trap
The same logic written with a correlated NOT EXISTS doesn't fall into the trap, because it never directly compares against the NULL; it only asks "does a matching row exist", and a NULL rating just never matches. When in doubt, prefer NOT EXISTS over NOT IN for exclusion logic.
Fixed: correctly excludes only the real Penang ratings
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:
| city | avg_rating |
|---|---|
| Kuala Lumpur | 4.55 |
Cities averaging above 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.
Identical result, clearer code
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.