Unit 4: Subqueries, CTEs and set operations
Use the result of one query inside another, break hard questions into named steps with WITH, walk trees with recursion, and stack results with UNION.
The big picture
A query's result is a table, so it can feed another query. That one idea turns hard questions ("customers who spend more than the average customer") into a chain of easy ones — and CTEs let you write that chain top to bottom, the way you would explain it.
Lessons in this unit
- Subqueries: a query inside a query: A subquery is a SELECT inside another query, in brackets: it can return one value (to compare with), a list (for IN), or a whole table (to select FROM).
- Correlated subqueries, EXISTS, and the NOT IN trap: A correlated subquery refers to a column of the outer row, so it is worked out once for each outer row. EXISTS asks "does the subquery return any row at all?", which makes it the safe way to test for a match.
- CTEs: naming each step with WITH: A CTE (common table expression) is a named, temporary result, written at the top of a query with WITH name AS (…). Later parts of the query can use it like a table.
- Recursive CTEs: walking trees and filling in dates: A recursive CTE refers to itself. An anchor query gives the starting rows. Then a recursive part, joined with UNION ALL, keeps adding rows from the previous round until no new rows appear.
- UNION, UNION ALL, INTERSECT and EXCEPT: Set operators stack the results of two queries. UNION ALL keeps every row, and UNION removes duplicates. INTERSECT keeps rows found in both, and EXCEPT keeps rows in the first but not the second.