MySQL - Common Table Expressions

MySQL - Common Table Expressions (WITH Clause)

A Common Table Expression, or CTE, is a named temporary result set that exists only for the duration of a single SQL statement (such as SELECT, INSERT, UPDATE, or DELETE). Introduced in MySQL 8.0, CTEs are defined using the WITH clause and are a powerful tool for improving the readability and structure of complex queries.

Why Use a CTE?

• Readability: CTEs allow you to break down long and complex queries into logical, easy-to-read blocks. This makes your SQL code easier to understand and maintain.
• Reusability: You can reference the same CTE multiple times within a single query, avoiding the need to repeat the same subquery logic.
• Simplifying Complex Joins and Aggregations: They allow you to pre-process or pre-aggregate data before joining it with other tables.
• Recursive Queries: CTEs can reference themselves, enabling you to query hierarchical data structures like organization charts or parts explosions. (This is an advanced topic not covered here).

Syntax of a Non-Recursive CTE

WITH cte_name_1 AS (
    -- Subquery defining the first CTE
    SELECT ...
),
cte_name_2 AS (
    -- Subquery defining the second CTE, can reference cte_name_1
    SELECT ...
)
-- Final query that uses the CTE(s)
SELECT ...
FROM cte_name_1
JOIN cte_name_2 ON ...;

Practical Examples

Let’s set up some sample tables to demonstrate practical use cases.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2)
);

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO departments VALUES (1, 'Sales'), (2, 'Engineering'), (3, 'HR');

INSERT INTO employees VALUES
(101, 'John', 'Doe', 2, 90000),
(102, 'Jane', 'Smith', 2, 95000),
(103, 'Peter', 'Jones', 1, 75000),
(104, 'Mary', 'Jane', 1, 80000),
(105, 'Sue', 'Storm', 3, 60000);

Example 1: Finding Employees Who Earn More Than Their Department’s Average Salary

Without a CTE, this would require a correlated subquery or a messy join. A CTE makes the logic crystal clear: first, calculate the average salary for each department, then join that result back to the employees table.

WITH DepartmentAverages AS (
    -- Step 1: Calculate the average salary for each department
    SELECT
        department_id,
        AVG(salary) as avg_salary
    FROM
        employees
    GROUP BY
        department_id
)
-- Step 2: Join the results back to find employees above the average
SELECT
    e.first_name,
    e.last_name,
    d.name AS department_name,
    e.salary,
    da.avg_salary
FROM
    employees e
JOIN
    DepartmentAverages da ON e.department_id = da.department_id
JOIN
    departments d ON e.department_id = d.id
WHERE
    e.salary > da.avg_salary;

Output

first_name	last_name	department_name	salary	avg_salary
Jane	Smith	Engineering	95000.00	92500.00
Mary	Jane	Sales	80000.00	77500.00

Example 2: Combining Data from Multiple Tables

This example demonstrates how to use multiple CTEs to pre-filter and shape data from different sources before the final join. This is a corrected and more meaningful version of the original tutorial’s multi-table example.

WITH
-- CTE 1: Select only senior engineers
SeniorEngineers AS (
    SELECT id, first_name, last_name, department_id
    FROM employees
    WHERE department_id = 2 AND salary >= 95000
),
-- CTE 2: Get department names
DepartmentInfo AS (
    SELECT id, name AS department_name
    FROM departments
)
-- Final Query: Join the two CTEs to get the final report
SELECT
    se.first_name,
    se.last_name,
    di.department_name
FROM
    SeniorEngineers se
JOIN
    DepartmentInfo di ON se.department_id = di.id;

Output

first_name	last_name	department_name
Jane	Smith	Engineering

Key Takeaways

• CTEs are available in MySQL 8.0 and later.
• They are defined using the WITH clause and are temporary, lasting only for the single statement that follows.
• Use CTEs to make your complex queries more modular, readable, and maintainable. They are a sign of modern, high-quality SQL.