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 form 8.0 onwards.