MySQL Common Table Expressions

One of the most powerful features added in MySQL version 8.0 is common table expressions, which allow for the construction of temporary result sets within a single SQL query. In our daily life queries, we often use common table expressions and it makes our work easier. In this article, we will understand the Common Table Expression with examples and also we will learn how to use the statements.

What is a Common Table Expression

A common table expression is a temporary result set that's named and which you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. Temporary result sets are usually referred to in the execution of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. CTEs are absolutely necessary to reduce complex queries down to standard readable and reusable pieces of code. Moreover, CTEs might be recursive—thereby permitting extremely complicated hierarchical or recursive queries.

Syntax of CTEs

The basic syntax of a CTE in MySQL is as follows:

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

where,

• WITH Keyword: This keyword introduces the CTE
• cte_name: This is the name of the CTE
• (column1, column2, ...): These are the column names that are passed.
• SELECT: Used to select the columns

Examples of MySQL Common Table Expressions

Here are some examples of MySQL Common Table Expressions:

Simplifying a Complex Query

Suppose that you have the table employees and you need to compute the total and average salary by department; then, among those, find the departments above an average salary of $60,000.

Create Table

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

Insert Data into Table

INSERT INTO employees (name, department, salary) VALUES
('John Doe', 'Sales', 55000.00),
('Jane Smith', 'Sales', 60000.00),
('Jim Brown', 'Sales', 65000.00),
('Jake White', 'Engineering', 75000.00),
('Jill Green', 'Engineering', 80000.00),
('Jenny Black', 'Engineering', 85000.00),
('James Gray', 'Marketing', 50000.00),
('Janet Blue', 'Marketing', 52000.00),
('Joan Pink', 'Marketing', 54000.00);

CTE Query

WITH department_salaries AS (
    SELECT department, 
           SUM(salary) AS total_salary, 
           AVG(salary) AS average_salary
    FROM employees
    GROUP BY department
)
SELECT department, total_salary, average_salary
FROM department_salaries
WHERE average_salary > 60000;

Output:

Explanation: The above example uses a Common Table Expression to determine the total and average salary by department from some Employees table. Here, the following CTE is named department_salaries. It aggregates the total and average salaries for each department. The main query then selects departments where the average salary exceeds $60,000. By using the CTE, the query becomes more readable and maintainable by breaking down this complex aggregation and filtering into clear, logical steps.

Recursive CTE for Hierarchical Data

Suppose these table categories represent a hierarchical category structure with a self-referencing foreign key parent_id.

Create Table

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

Insert Data Into Table

INSERT INTO categories (name, parent_id) VALUES
('Electronics', NULL),
('Computers', 1),
('Laptops', 2),
('Desktops', 2),
('Smartphones', 1),
('Accessories', 1),
('Chargers', 6),
('Cables', 6);

CTE Query to List All Subcategories

WITH RECURSIVE category_hierarchy AS (
    SELECT id, name, parent_id, 1 AS level
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ch.level + 1
    FROM categories c
    JOIN category_hierarchy ch ON c.parent_id = ch.id
)
SELECT id, name, parent_id, level
FROM category_hierarchy;

Output:

Explanation: This example shows the listing of all categories and their subcategories in a "categories table" where there is a self-referencing column for parent_id. Here, it uses a recursive Common Table Expression named category_hierarchy that starts off with the selection of top-level categories where parent_id is NULL, then recursively joins to include subcategories, incrementing a level column to show depth in hierarchy.

Temporary Aggregation

Let there be a table sales and you need to calculate the total sales for each salesperson, then filter those that achieved sales more than a given cutoff value.

Create Table

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    salesperson_id INT,
    sales_amount DECIMAL(10, 2)
);

Insert Data Into Tables

INSERT INTO sales (salesperson_id, sales_amount) VALUES
(1, 3000.00),
(1, 2500.00),
(1, 1500.00),
(2, 4000.00),
(2, 2000.00),
(3, 1000.00),
(3, 2000.00),
(4, 7000.00),
(5, 3000.00),
(5, 2500.00);

CTE Query

WITH total_sales AS (
    SELECT salesperson_id, SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY salesperson_id
)
SELECT salesperson_id, total_sales
FROM total_sales
WHERE total_sales > 5000;

Output:

Explanation: The example shows the use of Common Table Expressions in computing aggregate data and then reusing this aggregate inside a single query. Here, it creates a CTE called total_sales that computes the total amount of sales for each salesperson by grouping data from the sales table. It then selects from this total_sales CTE those salespersons with total sales greater than $5000.

Benefits of Using CTEs

Here are some benefits of the CTE:

• Readability: CTEs make difficult queries easier to understand, as they are broken down into smaller parts. It also provides clear readability of your SQL queries by naming the intermediate result sets, which any person not familiar with the specific query logic can use.
  
• Maintainability: You can move parts of a query into a CTE, and then changes to one part of the query will not affect the whole query; that is, modularity. This makes it easier for debugging and maintenance since every CTE can be understood and tested independently.
  
• Reusability: Once a CTE is defined, you can refer to it from other places within the same query. All this does is eliminate duplication, hence making your SQL code more compact. You do not need to repeat similar subquery logic in several places—a fact that not only reduces typing and increases readability but also lowers the possible error count.
  
• Recursive Queries: CTEs also support recursion and hence are perfect for any hierarchical data structure, from organizational charts to family trees, or bill-of-materials structures. Recursive CTEs can realize inquiries that would be very difficult or simply impossible to perform in regular SQL.
  
• Temporary Aggregation: One of the main use cases of CTEs is to provide temporary aggregations or calculations one might want to reference later in the main query. This improves the performance and clarity of a query by separating out the aggregation logic from the main query logic.

Conclusion

MySQL Common Table Expressions are used often by us in SQL queries. Whether it is creating a table or resolving any query you will need them. By the above article, you can understand Common Table Expressions easily with the help of the examples.