CTEs and Temporary Tables

Q: Q: Can multiple CTEs reference each other?

A: Yes. Multiple CTEs defined in the same WITH clause can reference earlier ones. However, they cannot cross-reference (CTE A references CTE B while CTE B also references CTE A).

🌍 Real-World Analogy

Imagine you're cooking a complex dish:

Subquery — Every time you need "chopped green onions," you have to chop them again (re-executed each time)
CTE — Chop the green onions first and put them in a bowl, then use them anytime later (named once, referenced multiple times)
Temporary table — Put the chopped ingredients on a temporary plate; not only can this dish use them, but they're available throughout the entire cooking session (accessible repeatedly within the session)

CTEs and temporary tables both follow the "prepare first, use later" approach, making complex queries clear and organized.

🎯 Core Concepts

WITH Clause / CTE

A CTE (Common Table Expression) uses the WITH keyword to define a temporary named result set that can be referenced like a table within the current query.

SQL

WITH cte_name AS (
    -- CTE query
    SELECT column1, column2 FROM table1
)
-- Main query references the CTE
SELECT * FROM cte_name;

Advantages:

Readability: Break complex queries into multiple logical steps
Reusability: The same CTE can be referenced multiple times in the main query
Recursion: Supports tree-structure traversal

Recursive CTE

A recursive CTE is defined with WITH RECURSIVE and contains two parts:

Anchor member: The initial query, the starting point of recursion
Recursive member: References itself, expanding step by step

SQL

WITH RECURSIVE cte_name AS (
    -- Anchor: initial query
    SELECT ... FROM table WHERE condition
    UNION ALL
    -- Recursive: references itself
    SELECT ... FROM table JOIN cte_name ON condition
)
SELECT * FROM cte_name;

Use cases: Organization charts, category directories, path finding, and other hierarchical data.

CTE vs Subquery

Feature	CTE	Subquery
Readability	Better (name first, then use)	Hard to read when deeply nested
Reusability	Multiple references	Re-executed each time referenced
Recursion	✅ Supported	❌ Not supported
Performance	Usually optimized to the same plan	Same
Scope	Current statement only	Current statement only

💡 Rule of thumb: When query logic is complex and you need to reference the same result set multiple times, prefer CTEs.

Temporary Tables — CREATE TEMPORARY TABLE

Temporary tables exist within the current database session and are automatically deleted when the session ends.

SQL

CREATE TEMPORARY TABLE temp_result (
    id INT,
    name VARCHAR(50),
    amount DECIMAL(10,2)
);

INSERT INTO temp_result SELECT ...;
SELECT * FROM temp_result;

Use cases:

Multiple SQL statements sharing the same intermediate result
Intermediate data in stored procedures
Needing to create indexes on intermediate results

CTE Use Case Summary

Scenario	Recommended Approach
Intermediate results in simple queries	Subquery
Breaking complex queries into steps	CTE
Tree/hierarchical data traversal	Recursive CTE
Sharing data across multiple SQL statements	Temporary table
Intermediate results need indexes	Temporary table

Traversing Tree Data with WITH RECURSIVE

Fields like department_id or self-referencing manager_id in an employee table can form hierarchical relationships. Recursive CTEs can easily traverse them:

SQL

-- Find all superiors of an employee (traverse upward)
-- Find all sub-departments under a department (traverse downward)
-- Generate a date series (number series)

📝 Basic Syntax

SQL

-- Basic CTE
WITH cte_name AS (
    SELECT ...
)
SELECT ... FROM cte_name;

-- Multiple CTEs
WITH cte1 AS (...),
     cte2 AS (...)
SELECT ... FROM cte1 JOIN cte2 ON ...;

-- Recursive CTE
WITH RECURSIVE cte_name AS (
    -- Anchor (starting point)
    SELECT ... FROM table WHERE initial_condition
    UNION ALL
    -- Recursive (references itself)
    SELECT ... FROM table 
    JOIN cte_name ON join_condition
    WHERE termination_condition
)
SELECT * FROM cte_name;

-- Temporary table
CREATE TEMPORARY TABLE temp_name AS
SELECT ... FROM ...;

-- Or create manually
CREATE TEMPORARY TABLE temp_name (
    col1 INT,
    col2 VARCHAR(50)
);

💡 Tip:

CTEs can only be used in the single SELECT/INSERT/UPDATE/DELETE statement that immediately follows

Recursive CTEs must have a termination condition, otherwise they will loop infinitely

Temporary table names cannot conflict with existing table names

MySQL uses CREATE TEMPORARY TABLE, SQL Server uses #temp_table

📌 Examples

Example: Using a CTE to Break Down a Complex Query — Department Per-Capita Sales Analysis

SQL

WITH dept_stats AS (
    SELECT 
        d.department_id,
        d.department_name AS department,
        COUNT(DISTINCT e.employee_id) AS employee_count,
        COUNT(o.order_id) AS order_count,
        COALESCE(SUM(o.total_amount), 0) AS total_sales
    FROM departments d
    LEFT JOIN employees e ON d.department_id = e.department_id
    LEFT JOIN orders o ON e.employee_id = o.employee_id AND o.status != 'cancelled'
    GROUP BY d.department_id, d.department_name
)
SELECT 
    department,
    employee_count,
    order_count,
    total_sales,
    CASE WHEN employee_count > 0 THEN ROUND(total_sales / employee_count, 2) ELSE 0 END AS sales_per_capita,
    CASE WHEN employee_count > 0 THEN ROUND(order_count * 1.0 / employee_count, 2) ELSE 0 END AS orders_per_capita
FROM dept_stats
ORDER BY sales_per_capita DESC;

▶ Try it Yourself

Result:

TEXT

department | employee_count | order_count | total_sales | sales_per_capita | orders_per_capita
-----------+----------------+-------------+-------------+------------------+------------------
Sales      |              2 |           4 |    15100.00 |          7550.00 |              2.00
Tech       |              2 |           3 |     8750.00 |          4375.00 |              1.50
Marketing  |              1 |           0 |        0.00 |             0.00 |              0.00
Finance    |              0 |           0 |        0.00 |             0.00 |              0.00

Explanation: The dept_stats CTE first aggregates the base data, then the main query calculates per-capita metrics. The logic is clear and easy to maintain.

Example: Recursive CTE — Employee Management Hierarchy

Suppose the employees table has a manager_id field indicating the direct manager:

SQL

-- First add the manager_id column and data
ALTER TABLE employees ADD COLUMN manager_id INT;

UPDATE employees SET manager_id = NULL WHERE employee_id = 1;  -- Zhang Wei: General Manager
UPDATE employees SET manager_id = 1    WHERE employee_id = 2;  -- Li Na -> Zhang Wei
UPDATE employees SET manager_id = 1    WHERE employee_id = 3;  -- Wang Qiang -> Zhang Wei
UPDATE employees SET manager_id = 3    WHERE employee_id = 4;  -- Zhao Min -> Wang Qiang
UPDATE employees SET manager_id = 3    WHERE employee_id = 5;  -- Liu Yang -> Wang Qiang
UPDATE employees SET manager_id = NULL WHERE employee_id = 6;  -- Chen Jing: No manager

-- Recursive query: starting from Zhang Wei, find all subordinates (traverse downward)
WITH RECURSIVE emp_hierarchy AS (
    -- Anchor: starting point (Zhang Wei)
    SELECT 
        employee_id,
        CONCAT(first_name, last_name) AS name,
        manager_id,
        0 AS level,
        CAST(CONCAT(first_name, last_name) AS CHAR(500)) AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: find subordinates
    SELECT 
        e.employee_id,
        CONCAT(e.first_name, e.last_name),
        e.manager_id,
        h.level + 1,
        CAST(CONCAT(h.path, ' → ', e.first_name, e.last_name) AS CHAR(500))
    FROM employees e
    INNER JOIN emp_hierarchy h ON e.manager_id = h.employee_id
)
SELECT 
    CONCAT(REPEAT('  ', level), name) AS org_chart,
    level,
    path
FROM emp_hierarchy
ORDER BY path;

▶ Try it Yourself

Result:

TEXT

org_chart      | level | path
---------------+-------+----------------------------
Zhang Wei      |     0 | Zhang Wei
  Li Na        |     1 | Zhang Wei → Li Na
  Wang Qiang   |     1 | Zhang Wei → Wang Qiang
    Zhao Min   |     2 | Zhang Wei → Wang Qiang → Zhao Min
    Liu Yang   |     2 | Zhang Wei → Wang Qiang → Liu Yang
Chen Jing      |     0 | Chen Jing

Explanation:

The anchor member finds all top-level employees with "no manager"
The recursive member finds subordinates level by level, with level + 1 recording the depth
REPEAT(' ', level) uses indentation to visually display the hierarchy
CAST(... AS CHAR(500)) prevents string truncation during recursion

🎬 Scenario Practice

Scenario 1: Calculating Customer Lifetime Value Step by Step

SQL

WITH order_summary AS (
    -- Step 1: Aggregate order data for each customer
    SELECT 
        customer_id,
        MIN(order_date) AS first_purchase,
        MAX(order_date) AS last_purchase,
        COUNT(order_id) AS order_count,
        SUM(total_amount) AS total_spent
    FROM orders
    WHERE status != 'cancelled'
    GROUP BY customer_id
),
customer_ltv AS (
    -- Step 2: Calculate lifetime days and spending frequency
    SELECT 
        customer_id,
        first_purchase,
        last_purchase,
        order_count,
        total_spent,
        DATEDIFF(last_purchase, first_purchase) AS lifetime_days,
        CASE 
            WHEN DATEDIFF(last_purchase, first_purchase) > 0 
            THEN ROUND(total_spent / (DATEDIFF(last_purchase, first_purchase) / 30.0), 2)
            ELSE total_spent
        END AS monthly_avg_spent
    FROM order_summary
)
SELECT 
    customer_id,
    first_purchase,
    last_purchase,
    order_count,
    total_spent,
    lifetime_days,
    monthly_avg_spent,
    CASE
        WHEN monthly_avg_spent >= 3000 THEN 'High Value'
        WHEN monthly_avg_spent >= 1000 THEN 'Medium Value'
        ELSE 'Low Value'
    END AS customer_tier
FROM customer_ltv
ORDER BY total_spent DESC;

Key Point: Multiple CTEs chained together, with clear logic at each step — aggregate first, calculate second, classify last. Far more readable than nested subqueries.

Scenario 2: Generating a Date Series for Sales Trend Analysis

SQL

WITH RECURSIVE date_series AS (
    -- Anchor: start date
    SELECT CAST('2026-01-01' AS DATE) AS dt
    UNION ALL
    -- Recursive: add 1 day each time
    SELECT DATE_ADD(dt, INTERVAL 1 DAY)
    FROM date_series
    WHERE dt < '2026-06-30'
)
SELECT 
    ds.dt AS date,
    COALESCE(COUNT(o.order_id), 0) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS sales
FROM date_series ds
LEFT JOIN orders o ON o.order_date = ds.dt AND o.status != 'cancelled'
GROUP BY ds.dt
ORDER BY ds.dt;

Key Point: A recursive CTE generates a continuous date series, then LEFT JOIN with the orders table ensures dates without orders are also displayed (with a value of 0), making it suitable for drawing trend charts.

❓ FAQ

Q: What's the difference between a CTE and a temporary table? A: A CTE is only valid within the current statement and disappears when the statement ends; a temporary table is valid for the entire session and can be referenced by multiple SQL statements. CTEs are suitable for breaking down a single complex query, while temporary tables are suitable for scenarios where intermediate results need to be accessed multiple times.

Q: Can a recursive CTE loop infinitely? A: Yes, if there's no proper termination condition. Ensure the recursive part has a WHERE condition to limit depth or scope. MySQL has a cte_max_recursion_depth parameter (default 1000) to prevent infinite recursion.

Q: Can a CTE be indexed? A: No, a CTE itself cannot have indexes created on it. If intermediate results need indexes for performance optimization, use a temporary table instead, as temporary tables support indexes.

Q: Can multiple CTEs reference each other? A: Yes. Multiple CTEs defined in the same WITH clause can reference earlier ones. However, they cannot cross-reference (CTE A references CTE B while CTE B also references CTE A).

📖 Summary

Technology	Scope	Reusable	Recursive	Indexable
Subquery	Single statement	❌	❌	❌
CTE	Single statement	✅ Multiple references	✅	❌
Temporary table	Entire session	✅ Multiple SQL statements	—	✅

CTEs are defined with WITH, improving readability and maintainability of complex queries
Recursive CTEs use WITH RECURSIVE, suitable for tree data traversal and sequence generation
Temporary tables are automatically deleted when the session ends, suitable for sharing intermediate results across SQL statements

📝 Exercises

Use a CTE to find the highest-paid employee in each department and show the difference between their salary and the department average.
Use a recursive CTE to generate a number series from 1 to 20.
Use a temporary table to store order summary data for each customer, then perform customer tier analysis based on the temporary table.
Thinking question: If a CTE is referenced twice by the main query, will it be executed twice? Verify with EXPLAIN.

Next Lesson

👉 21-transactions - Transaction Processing: Learn about SQL transaction ACID properties, BEGIN/COMMIT/ROLLBACK syntax, SAVEPOINT savepoints, transaction isolation levels, and the concept of deadlocks.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

CTEs and Temporary Tables

CTEs and Temporary Tables

🌍 Real-World Analogy

🎯 Core Concepts

WITH Clause / CTE

Recursive CTE

CTE vs Subquery

Temporary Tables — CREATE TEMPORARY TABLE

CTE Use Case Summary

Traversing Tree Data with WITH RECURSIVE

📝 Basic Syntax

📌 Examples

Example: Using a CTE to Break Down a Complex Query — Department Per-Capita Sales Analysis

Example: Recursive CTE — Employee Management Hierarchy

🎬 Scenario Practice

Scenario 1: Calculating Customer Lifetime Value Step by Step

Scenario 2: Generating a Date Series for Sales Trend Analysis

❓ FAQ

📖 Summary

📝 Exercises

Next Lesson

🙏 帮我们做得更好