SQL CTE (WITH): Make Long Queries Readable, Step by Step
A CTE gives one step of a query a name with WITH, so the next step can use it like a table. Build customer spend as a named step, join it to customers, and keep the big spenders, on a sample online shop database.
Step by step
Readable queries
- One step at a time. Long queries with brackets inside brackets are hard to read and harder to check. With lets you build a query one named step at a time. Let's find our big spenders that way.
- The orders table. Spend lives in the orders table, but names live in customers. So we need two steps.
Step one
- The first step. Step one on its own: total spend per customer. Sum of order total, grouped by customer ID. Thirty four customer I Ds with their totals. It works, so now we give it a name.
SELECT customer_id, SUM(order_total) AS total FROM orders GROUP BY customer_id;
Name the step
- WITH names it. Type with spend as, then the same query in brackets. Now spend works like a table. The last line simply reads from it. The same thirty four rows. Nothing changed in the answer, but the step now has a name we can build on.
WITH spend AS ( SELECT customer_id, SUM(order_total) AS total FROM orders GROUP BY customer_id) SELECT * FROM spend;
Step two
- Use the step. Step two adds the names. Join spend to customers. Using customer ID joins on the column both tables share. Thirty three, not thirty four. One order points at a customer who doesn't exist, so the join drops it. A good thing to report back.
SELECT first_name, last_name, total FROM spend JOIN customers USING (customer_id);
Step three
- Filter and sort. Last, keep customers who spent more than five hundred, biggest first. Fourteen big spenders, with names, biggest first. And you can read the query from top to bottom, like a recipe.
(customer_id) WHERE total > 500 ORDER BY total DESC;
Recap
- WITH in three steps. So, write one step and run it. Wrap it in with, give it a name, and use that name like a table in the next step. Long queries become short, readable steps.
