In a nutshell
A common table expression (CTE) is a temporary named result set that you can reference within a
DELETE statement. CTEs can make your queries more readable and performant.
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
- Common table expressions were introduced to MySQL 8.0 so they will only work from 8.0 onwards.