MySQL Common Table Expressions (CTE)

A Common Table Expression (CTE) is a named temporary result set defined at the beginning of a SQL query using the WITH keyword. It exists only for the duration of the query and can be referenced one or more times within that query. CTEs make complex queries easier to read, write, and maintain.

Why Use CTEs?

• Readability — Break a complex query into clearly named logical steps.
• Reusability — A CTE defined once can be referenced multiple times in the same query.
• Alternative to subqueries — CTEs are often cleaner than nested subqueries in the FROM clause.
• Foundation for recursion — CTEs are the basis for recursive queries in MySQL.

Syntax

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT *
FROM cte_name;

Sample Table

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2)
);

INSERT INTO employees VALUES (1, 'Ravi Kumar', 'IT', 72000);
INSERT INTO employees VALUES (2, 'Sneha Joshi', 'HR', 45000);
INSERT INTO employees VALUES (3, 'Karan Das', 'IT', 88000);
INSERT INTO employees VALUES (4, 'Pooja Nair', 'Finance', 61000);
INSERT INTO employees VALUES (5, 'Amit Roy', 'HR', 53000);
INSERT INTO employees VALUES (6, 'Deepa Rao', 'Finance', 67000);

Example: Simple CTE

WITH it_employees AS (
    SELECT emp_id, emp_name, salary
    FROM employees
    WHERE department = 'IT'
)
SELECT emp_name, salary
FROM it_employees
WHERE salary > 75000;

Result:

emp_name  | salary
----------+--------
Karan Das | 88000.00

CTE for Department Summary

WITH dept_summary AS (
    SELECT department,
           COUNT(*) AS total_employees,
           AVG(salary) AS avg_salary,
           MAX(salary) AS max_salary
    FROM employees
    GROUP BY department
)
SELECT department, total_employees, ROUND(avg_salary, 2) AS avg_salary, max_salary
FROM dept_summary
ORDER BY avg_salary DESC;

Multiple CTEs in One Query

Multiple CTEs can be defined in sequence, separated by commas.

WITH
high_earners AS (
    SELECT emp_id, emp_name, salary
    FROM employees
    WHERE salary >= 65000
),
dept_avg AS (
    SELECT department, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department
)
SELECT h.emp_name, h.salary, d.avg_sal, d.department
FROM employees AS e
JOIN high_earners AS h ON e.emp_id = h.emp_id
JOIN dept_avg AS d ON e.department = d.department;

CTE vs Subquery

CTE Availability

CTEs are supported in MySQL 8.0 and later. They are not available in MySQL 5.x.

Key Points

• A CTE is defined with WITH cte_name AS (...) before the main query.
• CTEs exist only for the duration of the query — they are not stored in the database.
• Multiple CTEs can be chained together using commas.
• CTEs improve query readability by naming logical steps clearly.
• CTEs support recursion — a feature not available with regular subqueries.