Common Table Expressions (WITH) in MySQL

Introduction

Common Table Expressions (CTEs) help you organize complex queries by creating temporary named result sets you can use inside your main query.

When you want to break a big query into smaller, easier parts.

When you need to reuse the same subquery multiple times in one query.

When you want to improve the readability of your SQL code.

When you want to write recursive queries, like finding all parts in a product hierarchy.

When you want to avoid repeating the same code in multiple places.

Syntax

MySQL

WITH cte_name AS (
  SELECT column1, column2
  FROM table_name
  WHERE condition
)
SELECT * FROM cte_name;

The CTE is defined after the WITH keyword and before the main SELECT.

You can use the CTE name like a temporary table in the main query.

Examples

This example creates a CTE named recent_orders to get orders after January 1, 2024, then selects all from it.

MySQL

WITH recent_orders AS (
  SELECT order_id, customer_id, order_date
  FROM orders
  WHERE order_date > '2024-01-01'
)
SELECT * FROM recent_orders;

This example finds customers who spent more than 1000 using a CTE, then selects their IDs.

MySQL

WITH top_customers AS (
  SELECT customer_id, SUM(amount) AS total_spent
  FROM sales
  GROUP BY customer_id
  HAVING SUM(amount) > 1000
)
SELECT customer_id FROM top_customers;

This example defines two CTEs for different departments and combines their results.

MySQL

WITH cte1 AS (
  SELECT * FROM employees WHERE department = 'Sales'
),
cte2 AS (
  SELECT * FROM employees WHERE department = 'HR'
)
SELECT * FROM cte1
UNION ALL
SELECT * FROM cte2;

Sample Program

This query uses a CTE named high_salary to find employees earning more than 70000, then lists them ordered by salary.

MySQL

WITH high_salary AS (
  SELECT employee_id, salary
  FROM employees
  WHERE salary > 70000
)
SELECT employee_id, salary FROM high_salary ORDER BY salary DESC;

Important Notes

CTEs exist only during the execution of the query; they do not create permanent tables.

You can define multiple CTEs separated by commas after WITH.

MySQL supports CTEs starting from version 8.0.

Summary

CTEs help organize and simplify complex SQL queries.

They create temporary named result sets used inside the main query.

CTEs improve code readability and can be reused within the query.