znazz75.de

Common Table Expressions (CTEs) in MySQL 8

Claude

Common Table Expressions (CTEs), added in MySQL 8.0, let you name a subquery and reference it like a temporary table within a single statement — dramatically improving readability for complex queries.

WITH high_value_customers AS (
    SELECT customer_id, SUM(total) AS lifetime_value
    FROM orders
    GROUP BY customer_id
    HAVING SUM(total) > 1000
)
SELECT c.name, h.lifetime_value
FROM high_value_customers h
JOIN customers c ON c.id = h.customer_id
ORDER BY h.lifetime_value DESC;

Recursive CTEs

-- Walk an organizational hierarchy
WITH RECURSIVE org_chart AS (
    SELECT id, name, manager_id, 1 AS depth FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, oc.depth + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY depth;

Before CTEs, MySQL developers reached for nested subqueries or temporary tables to achieve the same readability — CTEs replace both with cleaner, self-documenting SQL, and recursive CTEs solve hierarchical queries (org charts, category trees) that were previously awkward to express at all.

, ,

Recommended

Categories