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;

-- Compare monthly sales to the overall average.
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;

-- 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;

-- Expand the manager-to-subordinate hierarchy using a recursive CTE.
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;

-- Result of: WITH RECURSIVE seq AS (...) SELECT n FROM seq;
-- +----+
-- | n  |
-- +----+
-- |  1 |
-- |  2 |
-- |  3 |
-- | .. |
-- | 10 |
-- +----+

-- Result of the hierarchy expansion query
-- +-------+---------+
-- | depth | name    |
-- +-------+---------+
-- |     0 | Alice   |
-- |     1 | Bob     |
-- |     1 | Carol   |
-- |     2 | Dave    |
-- +-------+---------+

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;

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.