MySQL Common Table Expressions

CTEs (Common Table Expressions)

-- Simple CTE (WITH clause)
WITH active_users AS (
    SELECT id, name, email
    FROM users
    WHERE is_active = 1
)
SELECT au.name, COUNT(o.id) AS orders
FROM active_users au
LEFT JOIN orders o ON au.id = o.user_id
GROUP BY au.id;

-- Multiple CTEs
WITH
monthly_sales AS (
    SELECT MONTH(created_at) AS month, SUM(total) AS revenue
    FROM orders
    WHERE YEAR(created_at) = 2024
    GROUP BY MONTH(created_at)
),
avg_monthly AS (
    SELECT AVG(revenue) AS avg_rev FROM monthly_sales
)
SELECT month, revenue,
    ROUND(revenue / avg_rev * 100, 1) AS pct_of_avg
FROM monthly_sales, avg_monthly;

-- Recursive CTE (hierarchy)
WITH RECURSIVE org AS (
    SELECT id, name, manager_id, 0 AS level
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, org.level + 1
    FROM employees e JOIN org ON e.manager_id = org.id
)
SELECT * FROM org ORDER BY level;