MySQL Common Table Expression (CTE)

In MySQL, Common Table Expressions (CTEs) are a powerful feature that enhances query readability and structure. CTEs allow to create temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs, which were first introduced in MySQL 8.0, make complex queries easier to manage and understand, breaking them down into simpler parts. This article will delve into the concept of CTEs in MySQL, exploring their syntax, benefits, and practical examples to illustrate their utility in real-world scenarios.

What is a CTE?

A Common Table Expression (CTE) is a named temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs are defined using the WITH keyword, followed by a query that generates the result set. CTEs enable user to more easily write & maintain complex queries via increased readability & simplification.

Syntax

The basic syntax for a CTE is as follows:

WITH cte_name (column1, column2, ...)
AS
(
    -- CTE Query
    SELECT ...
)
SELECT ...
FROM cte_name;

Benefits of Using CTEs

• Improved Readability: CTEs allow you to break down complex queries into simpler, more readable parts.
• Reusability: A CTE can be referenced multiple times within the same query, reducing redundancy.
• Modularity: CTEs enable modular query design, making maintenance and updates easier.
• Recursive Queries: Recursive CTEs simplify writing hierarchical or tree-structured data queries.

Types of CTEs

There are two types of CTEs i.e. non-recursive and recursive.

1. Non-Recursive CTE

A non-recursive CTE is a Common Table Expression that does not refer to itself. It is used to simplify complex queries by breaking them down into smaller, more manageable parts.

Let’s start with a simple example. Suppose we have a table employees with the following structure.

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

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES
(1, 'John', 'Doe', 1, 5000),
(2, 'Jane', 'Smith', 1, 6000),
(3, 'Jim', 'Brown', 2, 5500),
(4, 'Jake', 'White', 2, 7000),
(5, 'Jill', 'Green', 3, 7500);

We want to find all employees with a salary greater than $5500. Using a CTE, we can write.

WITH HighSalaryEmployees AS (
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE salary > 5500
)
SELECT *
FROM HighSalaryEmployees;

This query first creates a CTE named HighSalaryEmployees that selects employees with a salary greater than $5500. Then, it selects all columns from this CTE.

2. Recursive CTE Example

A recursive CTE is a Common Table Expression that references itself. It is particularly useful for querying hierarchical or tree-structured data, such as organizational charts or folder structures.

Get NIRAJAN JHA’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Consider a table employees_hierarchy representing an organizational hierarchy.

CREATE TABLE employees_hierarchy (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    manager_id INT
);

INSERT INTO employees_hierarchy (employee_id, employee_name, manager_id) VALUES
(1, 'John', NULL),
(2, 'Jane', 1),
(3, 'Jim', 1),
(4, 'Jake', 2),
(5, 'Jill', 2);

To find all employees reporting to John, including indirect reports, we use a recursive CTE.

WITH RECURSIVE EmployeeTree AS (
    SELECT employee_id, employee_name, manager_id
    FROM employees_hierarchy
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.employee_name, e.manager_id
    FROM employees_hierarchy e
    INNER JOIN EmployeeTree et ON e.manager_id = et.employee_id
)
SELECT *
FROM EmployeeTree;

In this example, the recursive CTE EmployeeTree starts with employees who have no manager (John). It then recursively joins the employees_hierarchy table to find all employees reporting to John directly or indirectly.

Advanced Usage of CTEs

1. Multiple CTEs

CTEs can be chained together, allowing the results of one CTE to be used in another. This is useful for breaking down complex queries into more manageable steps.

WITH
HighSalaryEmployees AS (
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE salary > 5500
),
DepartmentSalaries AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.first_name, e.last_name, d.avg_salary
FROM HighSalaryEmployees e
JOIN DepartmentSalaries d ON e.department_id = d.department_id;

2. CTEs with Aggregate Functions

CTEs can simplify the process of using aggregate functions. For instance, you can calculate the total sales per department and then filter departments with total sales above a certain threshold.

WITH DepartmentSales AS (
    SELECT department_id, SUM(sales) AS total_sales
    FROM sales
    GROUP BY department_id
)
SELECT *
FROM DepartmentSales
WHERE total_sales > 10000;

3. Using CTEs in Data Transformation

CTEs are handy for transforming data before using it in further queries. For example, you can format dates or clean up strings within a CTE.

WITH FormattedDates AS (
    SELECT employee_id, DATE_FORMAT(hire_date, '%Y-%m-%d') AS formatted_date
    FROM employees
)
SELECT *
FROM FormattedDates;

Practical Examples of CTEs

1. Aggregating Data with CTEs

CTEs can help aggregate data in a structured way. For example, calculating the average salary by department.

WITH DepartmentAvgSalaries AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT department_id, avg_salary
FROM DepartmentAvgSalaries;

2. Combining CTEs with Window Functions

Combining CTEs with window functions can provide powerful analytical capabilities. For example, calculating running totals or moving averages.

WITH SalesData AS (
    SELECT employee_id, sales, sale_date
    FROM sales
)
SELECT employee_id, sales, sale_date,
       SUM(sales) OVER (ORDER BY sale_date) AS running_total
FROM SalesData;

3. CTEs for Data Cleansing

CTEs can be used to clean data before further processing. For example, removing duplicates or correcting data anomalies.

WITH CleanedData AS (
    SELECT DISTINCT *
    FROM raw_data
)
SELECT *
FROM CleanedData;

Comparison with Derived Tables and Subqueries

1. CTE vs Derived Tables

Derived tables are subqueries in the FROM clause. While both derived tables and CTEs can achieve similar results, CTEs offer better readability and reusability.

-- Derived Table Example
SELECT d.department_id, d.avg_salary
FROM (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) d;

-- Equivalent CTE Example
WITH DepartmentAvgSalaries AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT department_id, avg_salary
FROM DepartmentAvgSalaries;

2. CTE vs Subqueries

Subqueries can be nested in various parts of a SQL statement. CTEs often provide a clearer and more maintainable alternative.

-- Subquery Example
SELECT employee_id, first_name, last_name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Equivalent CTE Example
WITH AvgSalary AS (
    SELECT AVG(salary) AS avg_salary
    FROM employees
)
SELECT employee_id, first_name, last_name
FROM employees, AvgSalary
WHERE salary > avg_salary;

When to Use CTEs over Subqueries

CTEs are preferred when:

• You need to break down complex queries.
• You have reusable query parts.
• You need recursive queries.

Conclusion

Common Table Expressions (CTEs) in MySQL offer a versatile and powerful way to handle complex queries, making your SQL code more readable and maintainable. By using CTEs, you can break down intricate queries into manageable parts, reuse query logic, and perform recursive operations with ease. Whether you’re filtering data, performing aggregations, or dealing with hierarchical structures, CTEs can significantly enhance your SQL querying capabilities. Embrace CTEs to write cleaner, more efficient, and more understandable SQL queries.

Thanks for Reading!