When should I reach for a CTE instead of a subquery?
Reach for a common table expression when the query has more than one step to it, or when the same piece of work is used more than once.
So what is the practical difference? Four things.
The first one is that a common table expression has a name, so the query reads like a list of steps. You can see the shape of the work at the top, and then read the final statement on its own.
The second one is that you can use it more than once. A derived table has to be written out again the second time, and the engine works it out twice. A common table expression is worked out once.
The third one is that several of them can be chained, and each one can use the one before it. That is the shape of a query with several layers of logic.
The fourth one is that a common table expression can be recursive, which nothing else can be.
WITH customer_totals AS (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
),
big_customers AS (
SELECT customer_id, total
FROM customer_totals
WHERE total > 100
)
SELECT c.name, big.total
FROM big_customers big
JOIN customers c ON c.id = big.customer_id
ORDER BY big.total DESC;
So when should you not use one? When the query is a single step, because then the name is just extra lines. And when the engine cannot use the plan that the name implies, which happens when the common table expression is used once and the engine would rather inline it.
The next question in this topic shows the same calculation both ways.