MySQL 8 and Common Table Expressions – An Introduction

July 31, 2017

MySQL 8 will have Common Table Expressions also known as CTEs.  I have had people asking me when  MySQL would have CTEs for years and I am glad to report they are ready for testing.

What Does a CTE look like?

The keyword WITH is the big hint that the statement is a CTE.  For now think of a CTE as a collections of views or subqueries that you combine together for the desired results. After the WITH clause, one or more comma separated sub-clauses that are subqueries. And CTEs can reference other CTEs.  For a first example, let us combine the world_x example database’s city and country table to produce a list of all cities and their corresponding countries.

WITH
  cte1 as (select Name as CName, CountryCode as CCode from city),

  cte2 as (select Name as Name, Code from country)
SELECT cte1.CName, cte2.Name from cte1
  join cte2 on (cte1.CCode = cte2.Code)
  order by cte1.CName;

So cte1 gets all the city tables Name  and CountryCode data and cte2 grabs the country  Name and Code columns.  The CTE part of this query is in blue. The city CountryCode is a corresponding key to the country Code column.  The part of the query in green is how the CTE is queried. You could consider it a short lived temp table. And here is the abbreviated output.

+————————+———————-+
| CName               | Name              |
+————————+———————-+
| A Coruña (La Coruña) | Spain |
| Aachen              | Germany        |
| Aalborg             | Denmark        |
| Aba                    | Nigeria            |
……

Got a Simpler Example?

mysql>WITH cte as (SELECT ID, Name, District FROM city)
select Name, ID from cte limit 5;

+—————-+—-+
| Name      | ID |
+—————-+—-+
| Kabul      | 1 |
| Qandahar | 2 |
| Herat       | 3 |
| Mazar-e-Sharif | 4 |
| Amsterdam | 5 |
+—————-+—-+
5 rows in set (0.00 sec)

This second example has a CTE named cte (and the Nobel Prize for hyper creating naming goes to …). From this CTE we can then select what information we desire from it. Sp in this way it is kinda of like a View. In the above example, the CTE grabs three columns of data but from it we only take two columns. And you can name you CTE what every you want within the normal constraints.

Plus Recursive!

And CTEs can also be recursive. A recursive common table expression is a CTE with a subquery that refers to its own name. At the beginning it will proclaim WITH RECURSIVE. You will often find them used with hierarchical data such as organization charts and assembly build sheets. The following example counts down from 10

mysql> WITH RECURSIVE rcte (x) AS
(SELECT 10
UNION ALL
SELECT x – 1 FROM rcte WHERE x > 1)
SELECT * FROM rcte;
+——+
| x |
+——+
| 10 |
| 9 |
| 8 |
| 7 |
| 6 |
| 5 |
| 4 |
| 3 |
| 2 |
| 1 |
+——+
10 rows in set (0.00 sec)

Where to use CTEs

CTEs are often easier to read than embedded subqueries. They disappear after the query is executed which makes them easier to manage than using temp tables.  And many think they are easier to understand than subqueries which hopefully means easier maintenance.

Performance

So lets run EXPLAIN on the first example, minus the ORDER BY:

mysql> EXPLAIN with cte1 as (select Name as CName, CountryCode as CCode from city), cte2 as (select Name as Name, Code from country) select cte1.CName, cte2.Name from cte1 join cte2 on (cte1.CCode = cte2.Code);
+—-+————-+———+————+——–+—————+———+———+————————–+——+———-+——-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+———+————+——–+—————+———+———+————————–+——+———-+——-+
| 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4079 | 100.00 | NULL |
| 1 | SIMPLE | country | NULL | eq_ref | PRIMARY | PRIMARY | 9 | world_x.city.CountryCode | 1 | 100.00 | NULL |
+—-+————-+———+————+——–+—————+———+———+————————–+——+———-+——-+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings\g
+——-+——+——————————————————————————————————————————————————————————————————————+
| Level | Code | Message |
+——-+——+——————————————————————————————————————————————————————————————————————+
| Note | 1003 | /* select#1 */ select `world_x`.`city`.`Name` AS `CName`,`world_x`.`country`.`Name` AS `Name` from `world_x`.`city` join `world_x`.`country` where (`world_x`.`country`.`Code` = `world_x`.`city`.`CountryCode`) |
+——-+——+——————————————————————————————————————————————————————————————————————+
1 row in set (0.00 sec)

The show warnings lets us peek at the actual query the MySQL optimizer will run. The optimizer was able to rewrite the query as a fairly simple join. So in this case at least, the performance will be fine.

 

 

Advertisements