~/learn/SQL · Subqueries & CTEs
Subqueries and CTEs
Use the result of one query inside another.
why this matters
Hard questions become easy when you break them into steps. CTEs let you name each step, like variables in a program.
| idINTEGER | nameTEXT | countryTEXT | signup_dateTEXT |
|---|---|---|---|
| 1 | Ada Lovelace | UK | 2023-01-14 |
| 2 | Grace Hopper | USA | 2023-02-02 |
| 3 | Alan Turing | UK | 2023-02-19 |
| 4 | Katherine Johnson | USA | 2023-03-05 |
showing 4 of 8 rows · 4 columns
A subquery is a query nested inside another. A scalar subquery returns one value and can sit anywhere a value is allowed — for example comparing each row to an overall average.
A subquery returning a column can feed IN, and one returning rows can act as a derived table in the FROM clause.
Once queries get long, a common table expression (WITH ... AS) gives each step a name. It reads top to bottom instead of inside out, and you can chain several CTEs.
Try it: Lower the threshold to 200 and see who else qualifies.
CTEs as a recipe
WITH step1 AS (…),
step2 AS (… FROM step1 …)
SELECT … FROM step2;
Read top to bottom, like a recipe.Pro tip
Stuck on a query? Write the first step as a CTE, SELECT * FROM it to check, then add the next step.
key takeaways
- A subquery is a query inside another query.
- WITH name AS (…) names a step you can reuse.
- Build complex queries one CTE at a time.
check your understanding
Which finds products priced above the average price?
free practice
Build confidence with a question you can solve without Pro.