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.
Posted by Stoker 
Open Source DBA's Blog 