WITH

Lesson 16 of 21 · Part 4 · Stronger queries · about 15 minutes

The last lesson put a subquery in FROM and used it as a table. Once a query gets long, with parentheses inside parentheses, it gets hard to read. WITH lets you take that piece out first, give it a name, and use it further down.

A query that is hard to read

This query shows how much each customer who has ordered has spent. The big block in parentheses is the derived table s, which totals the spending of each customer number; the outer query then joins customers to get the names:

1SELECT c.name, s.spent2FROM customers AS c3JOIN (4    SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS spent5    FROM orders AS o6    JOIN order_items AS oi ON oi.order_id = o.id7    GROUP BY o.customer_id8) AS s ON s.customer_id = c.id9ORDER BY s.spent DESC;

It works, but to read it you have to jump into the parentheses to see what s is, then jump back out and carry on.

Name it first with WITH

The same query, written with WITH:

1WITH spending AS (2    SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS spent3    FROM orders AS o4    JOIN order_items AS oi ON oi.order_id = o.id5    GROUP BY o.customer_id6)7SELECT c.name, s.spent8FROM customers AS c9JOIN spending AS s ON s.customer_id = c.id10ORDER BY s.spent DESC;

WITH name AS (query) first defines a temporary result table, spending, and the SELECT below uses it like any other table. The result is exactly the same, but now you can read it from top to bottom: first total what each customer spent, then add the names. A named query like this is called a CTE (common table expression). With a good name, you can tell at a glance what it holds. In MySQL, WITH needs version 8.0 or later.

1WITH cheap AS (2    SELECT * FROM products WHERE price < 103)4SELECT COUNT(*) FROM cheap WHERE category = 'Stationery';

Using it twice

Which customers spent more than the average? That needs spending twice: once for the average, and once to compare each customer with it. With WITH, the query is written only once:

1WITH spending AS (2    SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS spent3    FROM orders AS o4    JOIN order_items AS oi ON oi.order_id = o.id5    GROUP BY o.customer_id6)7SELECT c.name, s.spent8FROM spending AS s9JOIN customers AS c ON c.id = s.customer_id10WHERE s.spent > (SELECT AVG(spent) FROM spending)11ORDER BY s.spent DESC;

Without WITH, the same big block would be copied into both FROM and the subquery, and every change would have to be made in two places.

Several at once

You can define several after WITH, separated by commas, with WITH written only once. Later ones can use earlier ones:

1WITH order_totals AS (2    SELECT order_id, SUM(quantity * unit_price) AS total3    FROM order_items4    GROUP BY order_id5),6shipped AS (7    SELECT t.order_id, t.total8    FROM order_totals AS t9    JOIN orders AS o ON o.id = t.order_id10    WHERE o.status = 'shipped'11)12SELECT COUNT(*) AS orders, SUM(total) AS revenue13FROM shipped;

order_totals totals each order, shipped picks the shipped ones out of it, and the end counts them and adds them up. Each piece does one thing, like steps in a recipe.

Only for this one query

A name defined with WITH belongs only to the query that follows it. At the semicolon, it's gone:

1WITH cheap AS (SELECT * FROM products WHERE price < 10)2SELECT COUNT(*) FROM cheap;3SELECT * FROM cheap;

ERROR 1146 (42S02): Table 'town.cheap' doesn't exist

The first query gives 4; the second is a new query, cheap no longer exists, and ERROR 1146 says there is no such table.

When to use it

• A short subquery used once: just write it in parentheses.
• A long subquery, one used several times, or a calculation in several steps: use WITH, one name per step.

There is also WITH RECURSIVE, which can follow a chain level by level (like an employee's manager's manager); this course doesn't cover it.

Practice

Write the query, then press Check. A hint costs nothing; the answer is there when you need it.

1SELECT order_id, quantity * unit_price AS amount2FROM order_items;

1WITH order_totals AS (2    SELECT order_id, SUM(quantity * unit_price) AS total3    FROM order_items4    GROUP BY order_id5)6SELECT order_id, total7FROM order_totals8ORDER BY order_id;

1WITH spending AS (2    SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS spent3    FROM orders AS o4    JOIN order_items AS oi ON oi.order_id = o.id5    GROUP BY o.customer_id6)7SELECT *8FROM spending;

What you learned

• WITH name AS (query) names a piece of a query first; below it, you use it like a table.
• Written with WITH, a long query reads step by step from top to bottom.
• When the same result is needed several times, WITH lets you write it once.
• Define several at once, separated by commas; later ones can use earlier ones.
• A WITH name only works in the one query that follows it.