WITH (CTE)

Define and use a CTE.

WITH cte_name AS (
    SELECT ...
)
SELECT * FROM cte_name;

Define multiple CTEs.

WITH
    cte_name1 AS (SELECT ...),
    cte_name2 AS (SELECT ... FROM cte_name1)
SELECT * FROM cte_name2;

Define a recursive CTE (MySQL 8.0+ / PostgreSQL).

WITH RECURSIVE cte_name AS (
    -- Initial query (anchor)
    SELECT ...
    UNION ALL
    -- Recursive query
    SELECT ... FROM cte_name WHERE termination_condition
)
SELECT * FROM cte_name;

The following sample code uses these tables.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50)
);
CREATE TABLE orders (
    id INT PRIMARY KEY,
    employee_id INT,
    total INT,
    ordered_at DATE
);

INSERT INTO employees VALUES
(1, 'user_a', 'org_a'), (2, 'user_c', 'Research'),
(3, 'user_e', 'org_a'), (4, 'user_b', 'org_a'),
(5, 'user_c', 'Field Ops');

INSERT INTO orders VALUES
(1, 1, 50000, '2025-01-15'), (2, 1, 80000, '2025-02-20'),
(3, 2, 120000, '2025-01-10'), (4, 3, 30000, '2025-03-05'),
(5, 1, 45000, '2025-03-10'), (6, 4, 60000, '2025-02-28');

Verify the table contents.

SELECT * FROM employees;
+----+--------+------------+
| id | name   | department |
+----+--------+------------+
|  1 | user_a | org_a      |
|  2 | user_c | Research   |
|  3 | user_e | org_a      |
|  4 | user_b | org_a      |
|  5 | user_c | Field Ops  |
+----+--------+------------+
5 rows in set

Verify the result with the following query:

SELECT * FROM orders;
+----+-------------+--------+------------+
| id | employee_id | total  | ordered_at |
+----+-------------+--------+------------+
|  1 |           1 |  50000 | 2025-01-15 |
|  2 |           1 |  80000 | 2025-02-20 |
|  3 |           2 | 120000 | 2025-01-10 |
|  4 |           3 |  30000 | 2025-03-05 |
|  5 |           1 |  45000 | 2025-03-10 |
|  6 |           4 |  60000 | 2025-02-28 |
+----+-------------+--------+------------+
6 rows in set

The following orders and employees tables are used in the examples below.

Compare monthly sales to the overall average.

EXTRACT is a function that extracts a specific part (year, month, day, etc.) from a date. For details, see EXTRACT / DATE_FORMAT.

WITH monthly_sales AS (
    SELECT
        EXTRACT(MONTH FROM ordered_at) AS month,
        SUM(total) AS sales
    FROM orders
    WHERE EXTRACT(YEAR FROM ordered_at) = 2025
    GROUP BY month
),
avg_sales AS (
    SELECT AVG(sales) AS average FROM monthly_sales
)
SELECT
    ms.month,
    ms.sales,
    av.average,
    ms.sales - av.average AS diff
FROM monthly_sales ms, avg_sales av
ORDER BY ms.month;
+-------+--------+------------+------------+
| month | sales  | average    | diff       |
+-------+--------+------------+------------+
|     1 | 170000 | 128333.333 |  41666.667 |
|     2 | 140000 | 128333.333 |  11666.667 |
|     3 |  75000 | 128333.333 | -53333.333 |
+-------+--------+------------+------------+
3 rows in set

Note: Listing multiple tables separated by commas in the FROM clause generates all combinations (same behavior as CROSS JOIN). Since avg_sales contains only one row here, the average is attached to each monthly row.

Generate a sequence from 1 to 10 using a recursive CTE (MySQL 8.0+).

WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 10
)
SELECT n FROM seq;
+----+
| n  |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+
10 rows in set

A recursive CTE first retrieves the base rows (the non-recursive part), then repeatedly generates new rows from those results (the recursive part). The following example expands the manager-to-subordinate hierarchy using a hierarchical employees table that has employee_id and manager_id columns.

-- Hierarchical employees table (with employee_id / manager_id columns)
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);
INSERT INTO employees VALUES
(1, 'user_a', NULL), (2, 'user_c', 1),
(3, 'user_b', 1), (4, 'user_e', 2);

WITH RECURSIVE org AS (
    SELECT employee_id, name, manager_id, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, org.depth + 1
    FROM employees e
    JOIN org ON e.manager_id = org.employee_id
)
SELECT depth, name FROM org ORDER BY depth, name;

+-------+--------+
| depth | name   |
+-------+--------+
|     0 | user_a |
|     1 | user_b |
|     1 | user_c |
|     2 | user_e |
+-------+--------+
4 rows in set

The non-recursive WITH ... AS syntax is supported by MySQL (8.0+), PostgreSQL, Oracle, SQL Server, and SQLite (3.8.3+).

-- Non-recursive CTE (common to all major databases).
WITH monthly_sales AS (
    SELECT
        EXTRACT(MONTH FROM ordered_at) AS month,
        SUM(total) AS sales
    FROM orders
    WHERE EXTRACT(YEAR FROM ordered_at) = 2025
    GROUP BY month
)
SELECT * FROM monthly_sales;

For recursive CTEs, MySQL, PostgreSQL, and SQLite require the WITH RECURSIVE keyword. Oracle and SQL Server support recursive CTEs without the RECURSIVE keyword.

-- Recursive CTE (MySQL / PostgreSQL / SQLite).
WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 10
)
SELECT n FROM seq;

-- Recursive CTE (Oracle / SQL Server) — no RECURSIVE keyword needed.
WITH seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 10
)
SELECT n FROM seq;

Common mistake 1: missing termination condition

A recursive CTE without a termination condition (WHERE) loops forever.

-- NG: No termination condition — infinite loop.
WITH RECURSIVE bad_seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM bad_seq
)
SELECT n FROM bad_seq;

OK: Always include a termination condition (WHERE).

WITH RECURSIVE good_seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM good_seq WHERE n < 10
)
SELECT n FROM good_seq;

+----+
| n  |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+
10 rows in set

Common mistake 2: duplicate CTE names

-- NG: Defining two CTEs with the same name in the same WITH clause causes an error.
WITH
    data AS (SELECT 1 AS x),
    data AS (SELECT 2 AS x) -- Error: duplicate CTE name.
SELECT * FROM data;

CTEs are most powerful for "aggregation of aggregations" (avoiding nested subqueries) and recursive hierarchy traversal.

-- Pattern 1: Filter to the top N rows without subqueries.
WITH monthly_totals AS (
    SELECT
        EXTRACT(MONTH FROM ordered_at) AS month,
        SUM(total) AS sales
    FROM orders
    GROUP BY month
),
top_months AS (
    SELECT month FROM monthly_totals
    ORDER BY sales DESC
    LIMIT 2
)
SELECT o.*
FROM orders o
WHERE EXTRACT(MONTH FROM o.ordered_at) IN (SELECT month FROM top_months);

-- Pattern 2: Generate a date sequence with a recursive CTE (useful for calendars).
WITH RECURSIVE date_seq AS (
    SELECT CAST('2025-01-01' AS DATE) AS dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY)
    FROM date_seq
    WHERE dt < '2025-01-07'
)
SELECT dt FROM date_seq;

+------------+
| dt         |
+------------+
| 2025-01-01 |
| 2025-01-02 |
| 2025-01-03 |
| 2025-01-04 |
| 2025-01-05 |
| 2025-01-06 |
| 2025-01-07 |
+------------+
7 rows in set

CTEs are especially useful for organizing complex queries that would otherwise require deeply nested subqueries in the FROM clause. A CTE defined once can be referenced multiple times within the same query, reducing code duplication.

Recursive CTEs are particularly handy for traversing tree structures such as org charts, category hierarchies, and parts explosions. Always include a termination condition (WHERE clause) to prevent infinite loops. In MySQL, you can control the maximum recursion depth with the max_sp_recursion_depth system variable.

To save a temporary query result as a reusable view, see VIEW. For combining result sets, see UNION / INTERSECT / EXCEPT.