Common table expression

What is a common table expression (CTE)?

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.

Examples

Single CTE

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.

Multiple CTEs

WITH cte1 AS (
    -- Your first CTE query
    SELECT ...
),
cte2 AS (
    -- Your second CTE query
    SELECT ...
),
cte3 AS (
    -- Your third CTE query
    SELECT ...
)
-- Final query using the CTEs
SELECT ...
FROM cte1
JOIN cte2 ON ...
JOIN cte3 ON ...

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