intro
Let’s dig into the MySQL CTE world, exploring what common table expressions are, how they work, and how they can help you write more readable queries.
Extracting necessary data from a MySQL database often requires running multiple queries. One approach is to execute several queries at the application level, which leads to significant overhead. Another option is to use subqueries. Unfortunately, these have notable limitations regarding reusability. The solution? The MySQL CTE mechanism can save your day!
In this article, you will explore the definition of CTEs, learn how to define them with the MySQL WITH clause, and see some real-world examples.
Let's dive in!
What Is a MySQL CTE?
In MySQL, a CTE—short for Common Table Expression—is a named temporary result set that exists within the scope of a single SELECT, INSERT, UPDATE, or DELETE statement.
The CTE mechanism is part of the ISO/ANSI SQL standard, and it was introduced to simplify complex queries by breaking them into more readable parts. CTEs also open the door to recursive queries in SQL.
While the definition between a CTE and a subquery may be similar, there is one major difference between the two. Unlike subqueries, the same CTE can be referenced multiple times within a query. This results in greater clarity and maintainability compared to SQL subqueries.
How to Define a CTE in MySQL Through the WITH Clause
In MySQL, CTEs are defined through the WITH clause using the following syntax:
1
WITH [RECURSIVE]
2
cte_name_1 [(col_name_a1 [, ..., col_name_z1])] AS (subquery_1)
3
[ ... ]
4
[, cte_name_n [(col_name_n1 [, ..., col_name_z1])] AS (subquery_n)]
5
sql_statement
Note that the same MySQL WITH clause can specify one or more CTEs. In particular, each CTE is defined through:
In the subquery definition, a CTE can refer to its own name. In this case, a MySQL CTE expression is considered recursive. If WITH contains at least one recursive CTE, the optional RECURSIVE keyword must be included. For more information, refer to the official documentation.
Now, see the WITH clause in action in the MySQL CTE example below:
1
WITH
2
cte_1 AS (SELECT column_1, column_2 FROM table_1),
3
cte_2 AS (SELECT column_3, column_4 FROM table_2)
4
SELECT column_2, column_4
5
FROM cte_1
6
JOIN cte_2 ON cte1.column_1 = cte2.column_3;
The WITH clause in the above query defines the CTEs cte_1 and cte_2. Then, the main SELECT statement after WITH references those CTEs to retrieve the desired data.
MySQL CTEs have a couple rules that need to be followed for our work with them not to become a headache. Many of you know that a CTE can reference another CTE, but there’s much more to it!
CTE Definition Rules
Here are some key rules to remember when defining a CTE in MySQL:
There are two possible scenarios for determining the column names for a CTE MySQL expression:
1
WITH cte (col_1, col_2) AS (
2
SELECT col_a, col_b
3
FROM table_x
4
)
5
SELECT col_1, col_2
6
FROM cte;
In this case, col_1 references col_a and col_2 references col_b. Note that the number of names in the list before AS must match the number of columns specified in subquery statement.
1
WITH cte AS (
2
SELECT col_a, col_b
3
FROM table_x
4
)
5
SELECT col_a, col_b
6
FROM cte;
In the above example, the columns of the CTE are col_a and col_b.
WITH Clause Rules
A WITH clause is permitted in these contexts:
Other rules to keep in mind when dealing with a MySQL WITH clause are:
MySQL CTE: Pros and Cons
Discover the benefits and drawbacks associated with the MySQL CTE mechanism.
Pros
Cons
CTE MySQL Examples
Explore three examples to understand how MySQL CTEs can be beneficial in real-world scenarios.
Note: The following sample queries will be executed in DbVisualizer, the database client renowned for its high user satisfaction on the market. Since queries with CTEs can become long, we will present an indented version of them generated using DbVisualizer's SQL formatting capabilities.
The first two examples refer to the employees table below:
Time to see some CTE MySQL examples!
Example 1: Finding the Department with the Highest Average Salary
For the first example, assume you want to identify the department with the highest average salary. You can obtain that information using an elegant query with a MySQL WITH clause, as follows:
1
WITH
2
department_avg_salary AS
3
( SELECT
4
department_id,
5
AVG(salary) AS avg_salary
6
FROM
7
employees
8
GROUP BY
9
department_id
10
)
11
SELECT
12
D.name,
13
DAS.avg_salary
14
FROM
15
department_avg_salary DAS
16
JOIN
17
departments D
18
ON
19
D.id = DAS.department_id
20
ORDER BY
21
DAS.avg_salary DESC
22
LIMIT 1;
Here, the CTE department_avg_salary calculates the average salary for each department. The main query then displays the department with the highest average salary using an SQL LIMIT clause.
The result will be:
While you could achieve the same information with a complex SELECT statement, using a CTE greatly improves the readability of the query.
Example 2: Getting an Organization’s Hierarchy
Suppose you want to retrieve the organizational hierarchy under a specific employee. Below is how you can achieve that using a recursive CTE:
1
WITH
2
**RECURSIVE** employee_hierarchy AS
3
( SELECT
4
id,
5
full_name,
6
manager_id
7
FROM
8
employees
9
WHERE
10
id = 4
11
12
UNION ALL
13
14
SELECT
15
E.id,
16
E.full_name,
17
E.manager_id
18
FROM
19
employees E
20
JOIN
21
employee_hierarchy EH
22
ON
23
EH.id = E.manager_id
24
)
25
SELECT
26
*
27
FROM
28
employee_hierarchy;
In this example, the MySQL CTE employee_hierarchy selects the employee with ID 4. Then, it recursively joins with the employees table to retrieve employees managed by the current manager, continuing to traverse up the hierarchy until the entire sub-hierarchy is fetched. This is a common query pattern for traversing a tree structure in MySQL.
Execute it, and you will get the following result:
Note that this result cannot be achieved with a simple SELECT statement: you would likely need to use subqueries instead.
Example 3: Retrieving Product Sales in Key Regions
Now, you want to get the products only in the regions with the highest sales. Achieve that with a WITH clause defining two auxiliary CTEs as follows:
1
WITH
2
regional_sales AS
3
( SELECT
4
region_id,
5
SUM(price) AS total_sales
6
FROM
7
orders
8
GROUP BY
9
region_id
10
)
11
,
12
top_regions AS
13
( SELECT
14
region_id
15
FROM
16
regional_sales
17
WHERE
18
total_sales >
19
( SELECT
20
SUM(total_sales) /
21
( SELECT
22
COUNT(*)
23
FROM
24
regional_sales)
25
FROM
26
regional_sales )
27
)
28
SELECT
29
R.name AS region,
30
P.name AS product,
31
SUM(O.quantity) AS product_units,
32
SUM(O.price) AS product_sales
33
FROM
34
orders O
35
JOIN
36
regions R
37
ON
38
R.id = O.region_id
39
JOIN
40
products P
41
ON
42
P.id = O.product_id
43
WHERE
44
O.region_id IN
45
( SELECT
46
region_id
47
FROM
48
top_regions)
49
GROUP BY
50
O.region_id,
51
O.product_id;
Let's break down what the above query does:
The result of the query will be:
North America is the only region with sales above the average
Wonderful! You are now a MySQL CTE master.
Conclusion
In this article, you saw what a MySQL CTE is and how it functions. You now know that CTEs serve as a powerful tool for enhancing code organization and promoting code reuse. Specifically, you explored the syntax of the MySQL WITH clause to define CTEs.
The main significant drawback of this feature is that it may lead to non-optimal, slow queries. This is where DbVisualizer comes in!
Beyond offering the essential features of a database client and supporting numerous DBMSs, DbVisualizer provides advanced query optimization capabilities that can elevate your MySQL CTEs to the next level. Download DbVisualizer for free now!
FAQ
Is it possible to define a CTE in MySQL 5.7?
While you can make use of subqueries, you cannot define a CTE in MySQL 5.7. The reason is that the WITH clause was only introduced in MySQL 8.0. Thus, you must upgrade to MySQL 8.0 or later to take advantage of the CTE MySQL mechanism.
What is the difference between common table expressions and recursive common table expressions?
Recursive common table expressions are a specific type of CTE that references themselves. In other terms, they enable the execution of recursive queries in SQL. A recursive CTE in MySQL consists of two SELECT statements in a UNION or UNION ALL clause. The first represents the anchor member for the base case, while the second is the recursive member that iteratively processes data.
Does MySQL support recursive CTEs?
Yes, MySQL supports recursive CTEs. This functionality is particularly useful for traversing tree structures, such as organizational charts.
What is the difference between a derived table and a CTE in MySQL?
A derived table and a CTE in MySQL both serve to structure subqueries, but they differ in certain ways. A MySQL derived table is a subquery used directly in the FROM clause and is only usable within that query:
1
SELECT ... FROM (subquery) [AS] table_name ...
Instead, a CTE is defined with the WITH clause and can be referenced multiple times within the same query. CTEs also offer recursive capabilities.
Is the MySQL WITH clause also available in other DBMS technologies?
Yes, WITH is part of the standard ISO/ANSI SQL standard. That means it is also available with the same syntax in other databases and SQL dialects. See it in action in Postgres in our guide on PostgreSQL CTE!
