This blog is the first part of a two-articles series. In this article, I’m going to introduce the Common Table Expression (CTE), a new feature available on MySQL 8.0, as well as Percona Server for MySQL 8.
What is a Common Table Expression?
We can define a CTE as an alternative to a derived table. In a small way, CTE simplifies complex joins and subqueries, improving the readability of the queries. CTE is part of ANSI SQL 99 and was introduced in MySQL 8.0.1. The same feature is available even on Percona Server for MySQL 8.0.
The main reasons for using CTE are:
- Better readability of the queries
- Can be referenced multiple times in the same query
- Improved performance
- A valid alternative to a VIEW, if your user cannot create VIEWs
- Easier chaining of multiple CTE
- Possibility to create recursive queries: this can be really useful when dealing with hierarchical data
SELECT, UPDATE, and DELETE statements can reference the CTE.
Note: for the examples in this article, I’ll use the world database you can download from the MySQL site.
How to Create and Use a CTE
We have said that a CTE is like a derived table when using a subquery, but the declaration is moved before the main query. A new dedicated clause is needed: WITH.
Let’s start with a subquery with a derived table to find the most populated countries in Europe:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> SELECT Name, Population -> FROM (SELECT * FROM country WHERE continent='Europe') AS derived_t -> ORDER BY Population DESC LIMIT 5; +--------------------+------------+ | Name | Population | +--------------------+------------+ | Russian Federation | 146934000 | | Germany | 82164700 | | United Kingdom | 59623400 | | France | 59225700 | | Italy | 57680000 | +--------------------+------------+ |
Let’s rewrite it using the CTE instead:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> WITH cte AS (SELECT * FROM country WHERE continent='Europe') -> SELECT Name, Population -> FROM cte -> ORDER BY Population DESC LIMIT 5; +--------------------+------------+ | Name | Population | +--------------------+------------+ | Russian Federation | 146934000 | | Germany | 82164700 | | United Kingdom | 59623400 | | France | 59225700 | | Italy | 57680000 | +--------------------+------------+ |
The syntax is quite simple. Before your query, using WITH, you can define the CTE or even multiple CTEs. After that, you can reference in the query all the CTEs as many times as you need. You can think about a CTE as a sort pre-materialized query available as a temporary table for the scope of your main query.
We can also specify the column names if a parenthesized list of names follows the CTE name:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> WITH cte(eur_name, eur_population) AS (SELECT Name, Population FROM country WHERE continent='Europe') -> SELECT eur_name, eur_population -> FROM cte -> ORDER BY eur_opulation DESC LIMIT 5; +--------------------+----------------+ | eur_name | eur_population | +--------------------+----------------+ | Russian Federation | 146934000 | | Germany | 82164700 | | United Kingdom | 59623400 | | France | 59225700 | | Italy | 57680000 | +--------------------+----------------+ |
CTE can also be used as the data source for updating other tables as in the following examples:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
# create a new table mysql> CREATE TABLE country_2020 ( Code char(3), Name char(52), Population_2020 int, PRIMARY KEY(Code) ); Query OK, 0 rows affected (0.10 sec) # copy original data mysql> INSERT INTO country_2020 SELECT Code, Name, Population FROM country; Query OK, 239 rows affected (0.01 sec) Records: 239 Duplicates: 0 Warnings: 0 # increase population of european countries by 10% mysql> WITH cte(eur_code, eur_population) AS (SELECT Code, Population FROM country WHERE continent='Europe') -> UPDATE country_2020, cte -> SET Population_2020 = ROUND(eur_population*1.1) -> WHERE Code=cte.eur_code; Query OK, 46 rows affected (0.01 sec) Rows matched: 46 Changed: 46 Warnings: 0 # delete from the new table all non-europian countries mysql> WITH cte AS (SELECT Code FROM country WHERE continent <> 'Europe') -> DELETE country_2020 -> FROM country_2020, cte -> WHERE country_2020.Code=cte.Code; Query OK, 193 rows affected (0.02 sec) mysql> SELECT * FROM country_2020 ORDER BY Population_2020 DESC LIMIT 5; +------+--------------------+-----------------+ | Code | Name | Population_2020 | +------+--------------------+-----------------+ | RUS | Russian Federation | 161627400 | | DEU | Germany | 90381170 | | GBR | United Kingdom | 65585740 | | FRA | France | 65148270 | | ITA | Italy | 63448000 | +------+--------------------+-----------------+ |
CTE can also be used for INSERT … SELECT queries like the following:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
mysql> CREATE TABLE largest_countries (Code char(3), Name char(52), SurfaceArea decimal(10,2), PRIMARY KEY(Code) ); Query OK, 0 rows affected (0.08 sec) mysql> INSERT INTO largest_countries -> WITH cte AS (SELECT Code, Name, SurfaceArea FROM country ORDER BY SurfaceArea DESC LIMIT 10) |