WITH Clauses

WITH Clauses

Common Table Expressions with WITH

A CTE is a named subquery written before the statement that uses it, WITH name [(columns)] AS (SELECT ...); several are separated by commas, and each may read the earlier ones, so a query reads top to bottom instead of inside out. Who brought the most revenue, and what share?

Two chained CTEs, one of them read twiceSQL

WITH order_totals AS (
  SELECT o.id, o.customer_id, SUM(i.quantity * i.unit_price) AS total
  FROM orders AS o JOIN order_items AS i ON i.order_id = o.id
  WHERE o.status <> 'cancelled' GROUP BY o.id),
customer_totals (customer_id, spent) AS (
  SELECT customer_id, SUM(total) FROM order_totals GROUP BY customer_id)
SELECT c.name, t.spent,
       ROUND(100 * t.spent / (SELECT SUM(total) FROM order_totals), 1) AS pct
FROM customer_totals AS t JOIN customers AS c ON c.id = t.customer_id
ORDER BY t.spent DESC LIMIT 2;

Output

+------------+--------+------+
| name       | spent  | pct  |
+------------+--------+------+
| Ana Souza  | 151.40 | 24.9 |
| Ben Carter | 142.46 | 23.4 |
+------------+--------+------+
2 rows in set (0.002 sec)

order_totals is read twice, which a derived table cannot do, and a materialized CTE is built once however often it is read. The list after customer_totals names its columns.

CTEs arrived in MySQL 8.0 524 . A WITH may also open an UPDATE or DELETE: WITH low AS (SELECT id FROM reviews WHERE rating <= 2) DELETE FROM reviews WHERE id IN (SELECT id FROM low) deletes review 7, where the same subquery without the CTE raises error 1093. Only one WITH is allowed per query level; WITH a AS (...) WITH b AS (...) is a syntax error.

MENU