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. Here it finds the big spenders in a sample online shop database.
- The orders table. Spend is in the orders table, but customer names are in the customers table. So the query needs two steps.
Step one
- The first step. Step 1 on its own: total spend per customer. SUM(order_total) AS total adds up each customer's orders, and GROUP BY customer_id gives one row per customer. Run it: 34 customer IDs, each with their total. The step works, so now it can get 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, without its semicolon. Now spend works like a table, and the last line, SELECT * FROM spend;, simply reads from it. Run it: the same 34 rows. The answer hasn't changed, but the step now has a name you 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. Replace the last line with a join to customers to add the names. USING (customer_id) joins on the column both tables share. It works in PostgreSQL, MySQL 8.0 or later, SQLite and Oracle. SQL Server has no USING, so write ON spend.customer_id = customers.customer_id. Run it: 33 rows, not 34. One order has a customer ID that isn't in the customers table, so the join drops it. That's worth reporting back as a data problem.
SELECT first_name, last_name, total FROM spend JOIN customers USING (customer_id);
Step three
- Filter and sort. Last step: after USING (customer_id), add WHERE total > 500 ORDER BY total DESC. This keeps customers who spent more than 500 and puts the biggest first. Run it: 14 big spenders, with names, biggest first. The query now reads from top to bottom like a recipe: total the spend, add the names, then filter and sort.
(customer_id) WHERE total > 500 ORDER BY total DESC;
Recap
- WITH in three steps. So: write 1 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.
Common mistakes
- Leaving the semicolon inside the brackets. The query inside WITH spend AS ( ... ) must not end with a semicolon, because that ends the whole statement too early. Only the final SELECT gets the semicolon.
- Using the CTE name in a separate query. A CTE only exists for the one statement it belongs to. If you run SELECT * FROM spend; on its own later, you get an error saying the table doesn't exist. Keep the WITH part at the top of every query that uses it.
- Expecting the join to keep every row. A plain JOIN drops spend rows that have no matching customer, which is why 34 became 33. Use spend LEFT JOIN customers if you want to keep them and see blank names.
- Filtering the sum with WHERE in step 1. WHERE can't filter on SUM(order_total) in the same query that calculates it. You would need HAVING there. Once the total sits in a CTE it's a normal column, so WHERE total > 500 works.
When a business analyst uses this
Before a workshop about a loyalty scheme, a BA can check the real numbers: one CTE totals spend per customer, the next step adds names, and the last keeps customers over the threshold. The order with no matching customer goes on the issues log for the data owner to fix.
Questions people ask
What does CTE stand for in SQL?
CTE stands for common table expression. It's a named query you write after WITH, which the rest of the statement can read from like a table.
What is the difference between a CTE and a subquery?
A CTE does the same job as a subquery in the FROM clause, but it has a name and sits at the top, so the query reads in order. You can also refer to the same CTE more than once in that query.
How do you use more than one CTE in a query?
Write WITH once and separate the steps with commas, for example WITH spend AS (...), big AS (SELECT * FROM spend WHERE total > 500). Each step can read from the steps above it.
