CTEs and set operations
As questions get harder, queries get longer. Two tools keep them readable: common table expressions name a subquery so you can build a query in steps, and set operations stack the results of several queries into one. Both boxes below are live.
Common table expressions (CTE)
A subquery buried inside another query is hard to read inside-out. A CTE lifts it out, gives it a name with WITH, and lets the main query read it like a table. Same result as a subquery - far easier to follow.
Read it top-down: first build spend (one row per customer with their total), then join it to customers and filter. You can define several CTEs separated by commas, and each can build on the one before. A CTE can even reference itself - a recursive CTE - to walk hierarchies like an org chart or a category tree (a topic for later).
Set operations: stacking results
Set operations combine the rows of two SELECTs that return the same columns. Think of them as the maths of result sets.
UNION- all rows from both, with duplicates removed.UNION ALL- all rows from both, duplicates kept. Faster, because it skips the de-duplication step - use it when you know there are no duplicates or you want them.INTERSECT- only rows that appear in both.EXCEPT- rows in the first query but not the second.
"Which customers have placed an order?" is an intersection of customer ids:
"Which customers have never ordered?" is the difference - try swapping EXCEPT for INTERSECT above, or run:
CTE, view, or subquery?
All three let you reuse a query result, but they differ in scope and lifetime.
| CTE | View | Subquery | |
|---|---|---|---|
| What it is | Named temporary result inside one statement | A saved query stored as a database object | A query nested inside another |
| Defined with | WITH name AS (...) | CREATE VIEW name AS ... | parentheses, inline |
| Lives | Only for that one statement | Until you drop it; reusable across queries | Only where it sits |
| Best for | Breaking one complex query into readable steps | A query many queries reuse | A small one-off computation |
Rule of thumb: reach for a subquery for a quick one-off, a CTE when one query gets long enough to hurt, and a view when several different queries need the same shaped result.
Quick quiz
Composing queries
3 questions1What is the difference between UNION and UNION ALL?
2What does a common table expression (CTE) give you?
3You need "customers who appear in both the loyalty list and the orders table." Which set operation fits?
Window functions - running totals, ranks, and row-to-row comparisons across a window of rows, without collapsing them.