SQL: Common table expression reference

In a nutshell

A common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs can make your queries more readable and performant.

Example

WITH cte AS (
  SELECT customer_id, SUM(order_total) as total_spent
  FROM orders
  GROUP BY customer_id
  HAVING SUM(order_total) > 1000
)
SELECT customers.customer_name, cte.total_spent
FROM customers
JOIN cte ON customers.customer_id = cte.customer_id;

You create a CTE by starting with the WITH statement and then defining a subquery. You can then reference the CTE in a SELECT, INSERT, UPDATE, or DELETE statement.

Other notes

• Common table expressions were introduced to MySQL 8.0 so they will only work from 8.0 onwards.

Sources

• An introduction to MySQL CTE – mysqltutorial.org