MySQL 8 Common Table Expressions CTE

Mughees Ahmed

4 years ago

MySQL 8 supports common table expressions, both non-recursive and recursive, A CTE (Common Table Expression) is a temporary result set that you can reference within another SELECT, INSERT, UPDATE, or DELETE statement.

Non-recursive CTE

A common table expression(CTE) is just like a derived table, but its declaration is put before the query block instead of in FROM clause. Using CTE, the subquery is evaluated only once, Common table expressions enable the use of named temporary result sets, Common Table Expressions are defined within the statement using the WITH operator.

Suppose you want to find out the percentage change in the payments of each year with respect to the previous year. Without CTE, you need to write two subqueries, and they are essentially the same. MySQL is not smart enough to detect that and the subqueries are executed twice.

SELECT 
q1.years,
q2.years AS next_year,
q1.sum1,
q2.sum1 AS next_sum,
100 * (q2.sum1 - q1.sum1) / q1.sum1 AS pct
FROM
(SELECT YEAR(paymentDate) AS years, SUM(amount) AS sum1 FROM payments GROUP BY years) AS q1,
(SELECT YEAR(paymentDate) AS years, SUM(amount) AS sum1 FROM payments GROUP BY years) AS q2
WHERE
q1.years = q2.years - 1;
+-------+-----------+------------+------------+------------+
| years | next_year | sum1 | next_sum | pct |
+-------+-----------+------------+------------+------------+
| 2003 | 2004 | 3250217.70 | 4313328.25 | 32.708903 |
| 2004 | 2005 | 4313328.25 | 1290293.28 | -70.085901 |
+-------+-----------+------------+------------+------------+
2 rows in set (0.01 sec)

With non-recursive CTE, the derived query is executed only once and reused

WITH CTE_NAME AS 
(SELECT YEAR(paymentDate) AS years, SUM(amount) AS sum1 FROM payments GROUP BY years)
SELECT 
q1.years,
q2.years AS next_year,
q1.sum1,
q2.sum1 AS next_sum,100 * (q2.sum1 - q1.sum1) / q1.sum1 AS pct 
FROM 
CTE_NAME AS q1,
CTE_NAME AS q2 
WHERE q1.years = q2.years - 1;
+-------+-----------+------------+------------+------------+
| years | next_year | sum1 | next_sum | pct |
+-------+-----------+------------+------------+------------+
| 2003 | 2004 | 3250217.70 | 4313328.25 | 32.708903 |
| 2004 | 2005 | 4313328.25 | 1290293.28 | -70.085901 |
+-------+-----------+------------+------------+------------+
2 rows in set (0.00 sec)

You may notice that with CTE, the results are the same and query time improves by 50%, the readability is good and can be referenced multiple times

CTEs can refer to other CTEs:
WITH cte1 AS (SELECT ... FROM ...), cte2 AS (SELECT ... FROM cte1 ...)
SELECT
FROM cte1, cte2 ...

Recursive CTEs

A recursive CTE is a CTE that references itself. In doing so, the initial CTE is repeatedly executed, returning subsets of data, until the complete result is returned

WITH RECURSIVE cte_name 
AS
(
cte_definition -- /* seed SELECT */
UNION ALL
cte_definition -- /* "recursive" SELECT */ references cte_name.
)
-- Statement using the CTE
SELECT *
FROM cte_name

Seed SELECT is executed once to create the initial data subset; recursive SELECT is repeatedly executed to return subsets of data until the complete result set is obtained. Recursion stops when an iteration does not generate any new rows.

Suppose you want to do hierarchical data traversal to produce an organizational chart with the management chain for each employee (that is, the path from CEO to an employee). Use a recursive CTE! Recursive CTE’s are well suited to querying hierarchical data,

Create table

CREATE TABLE mangeremp (
id INT PRIMARY KEY NOT NULL,
name VARCHAR(100) NOT NULL,
man_id INT NULL,
INDEX (man_id),
FOREIGN KEY (man_id) REFERENCES mangeremp (id)
);

insert data in order to get hierarchical structure

INSERT INTO mangeremp VALUES
(333, "waqas", NULL), # waqas is the CEO (man_id is NULL)
(198, "ali", 333), # ali has ID 198 and reports to 333 (waqas)
(692, "ahmed", 333), #ahmed report to waqas
(29, "oasama", 198), #osama report to ali as alo has ref id 198
(4610, "Mughees", 29), # Mughees report to osama
(72, "aslam", 29),
(123, "afrooz", 692);

WITH RECURSIVE emp_paths (id, name, path) AS 
(SELECT id, name, CAST(id AS CHAR(200)) 
FROM mangeremp 
WHERE man_id IS NULL 
UNION ALL 
SELECT e.id, e.name, CONCAT(ep.path, ',', e.id) 
FROM emp_paths AS ep JOIN mangeremp AS e 
ON ep.id = e.man_id )
SELECT * FROM emp_paths ORDER BY path;
+------+---------+-----------------+
| id | name | path |
+------+---------+-----------------+
| 333 | waqas | 333 |
| 198 | ali | 333,198 |
| 29 | oasama | 333,198,29 |
| 4610 | Mughees | 333,198,29,4610 |
| 72 | aslam | 333,198,29,72 |
| 692 | ahmed | 333,692 |
| 123 | afrooz | 333,692,123 |
+------+---------+-----------------+
7 rows in set (0.00 sec)

SELECT e.id, e.name, CONCAT(ep.path, ',', e.id) FROM emp_paths AS ep JOIN mangeremp AS e ON ep.id = e.man_id ---- recursive query

Each row produced by the recursive query finds all employees who report directly to an
employee produced by a previous row. For every such employee, the row includes the
employee ID, name, and employee management chain. The chain is the manager’s chain
with the employee ID added at the end