Common Table Expression (CTE) MySQL 8.0.

1.0. Introduction:

MySQL 8.0 was released with awesome features. One of its most prominent features is CTE (Common Table Expression).

The Common Table Expression can be used to construct complex queries in a more readable manner. In this blog, I have described how the CTE works with some useful examples.

1.1 What is CTE?

A Common Table Expression (CTE) is the result set of the query, which exists temporarily and uses only within the context of a larger query.

The CTE provides better readability and performance in comparison with a derived table.

In a normal query the temporary result set that exists only within the execution scope of a single SQL statement.

Example:

select city.ID,city.Name,city.Population from city 
where city.Population between '10000' and '500000' 
group by city.ID UNION select country.Code,country.Name,country.Population 
from country join city on city.ID=country.Code 
group by country.Capital order by Population;

Graphical View:

Visual Explain using MySQL Workbench

When Using CTE:

• The CTE can be self-referencing or it can be referenced multiple times in the same query.
• While comparing the normal query it will give better performance.

Example :

with cte_ex (ID,Name,Population) as 
( 
select ID,Name,Population from city  where Population between '10000' and '500000'  group by ID UNION select country.Code,country.Name,country.Population  from country join city on city.ID=country.Code  
)
select * from cte_ex group by ID order by Population;

Graphical View :

Reusability:

Just like database views and derived tables, CTE enables users to easily write and maintain complex queries with better readability and simplification. This reduction in complexity is achieved by deconstructing ordinarily complex queries into simple blocks to be used and reused if necessary.

1.2 CTE Syntax:

WITH cte_name (column_list) AS (
   --  Sub Query --
)
SELECT * FROM cte_name;

• Initiate a CTE using “WITH”.
• Provide a name for the result soon-to-be defined query.
• After assigning a name, follow with “AS”.
• Specify column names (optional step).
• Define the query to produce the desired result set.
• If multiple CTEs are required, initiate each subsequent expression with a comma.
• Reference the above-defined CTE(s) in a subsequent query.

Thumb Rules :

• The number of columns in the query must be the same as the number of columns in the column_list.
• If you omit the column_list, the CTE will use the column list of the query that defines the CTE.

1.3 Use Cases:

Needing to reference a derived table multiple times in a single query.

• An alternative to creating a view in the database.
• Performing the same calculation multiple times over across multiple query components.

Example:

WITH country_surface AS (
select
      Region, HeadOfState, SurfaceArea
from
      country
WHERE
      Region like 'A%'
) SELECT
      Region,SurfaceArea
FROM
     country_surface
order by SurfaceArea;

CTE Structure :

Output :

 
+---------------------------+-------------+
| Region                    | SurfaceArea |
+---------------------------+-------------+
| Australia and New Zealand |       14.00 |
| Australia and New Zealand |       36.00 |
| Antarctica                |       59.00 |
| Australia and New Zealand |      135.00 |
| Antarctica                |      359.00 |
| Antarctica                |     3903.00 |
| Antarctica                |     7780.00 |
| Australia and New Zealand |   270534.00 |
| Australia and New Zealand |  7741220.00 |
| Antarctica                | 13120000.00 |
+---------------------------+-------------+
10 rows in set (0.00 sec)

In this example name of the CTE is country_surface, the query that defines the CTE returns two columns Region and SurfaceArea.

1.4 WITH clause usages :

There are three usages WITH clause to make common table expressions.

1) A WITH clause can be used at the beginning of SELECT, UPDATE, and DELETE statements.

WITH ... select ...
WITH ... update ...
WITH ... delete ...

Source to update other tables :

The CTE provides a simple solution to update the other tables.

Example :

with cte as 
(
select store_id from inventory as a
)  
update store as b,cte set b.last_update='2018-10-15 00:00:00'  where b.store_id = cte.store_id;

Query OK, 2 rows affected (0.05 sec) 
Rows matched: 2  Changed: 2  Warnings: 0

2) A WITH clause can be used at the beginning of a subquery or a derived table subquery.

select ... where id IN (WITH ... select ...);

select * from (WITH ... select ...) AS derived_table;

3) A WITH clause can be used immediately preceding SELECT of the statements that include a SELECT clause.

create table ... WITH ... select ...
create view ... WITH ... select ...
insert ... WITH ... select ...
replace ... WITH ... select ...
declare cursor ... WITH ... select ...
explain ... WITH ... select ...

Example :

mysql> create table mydbops_test (id int(11) DEFAULT NULL,name varchar(30),address varchar(30),city varchar(15),country varchar(25));
Query OK, 0 rows affected (0.14 sec)

mysql> insert mydbops_test (id,name,address,city,country) 
       with RECURSIVE staff as 
(
       select id,name,address,city,country from staff_list 
)
select * from staff;
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0

1.5 Limiting CTE :

• It is important for recursive CTEs that the SELECT part include a condition to terminate the recursion.
• You can force termination by placing a limit on CTE execution time.
• max_execution_time it enforces an execution timeout for SELECT statements executed within the current session.
• The default max_execution_time is 0 .The variables only apply to read-only SELECT statements.

1.6 CTE Optimizer Hints :

We can include the max_execution_time an optimizer hint within the CTE statement.

with RECURSIVE full_table_scan as
(
select actors,title from film_list UNION ALL select actors,title from film_list where title='ACADEMY DINOSAUR'
)
SELECT /*+ MAX_EXECUTION_TIME(10) */ * FROM full_table_scan;

ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

1.7 Conclusion :

• The Common Table Expressions is one of the long-awaited features in MySQL 8.
• It will reduce more complexity. The purpose of CTEs is to simplify the writing of complex SQL queries.
• You can always recognize them by the “With” keyword at the start of the SQL statement.

Featured Image Courtesy : Photo by Jeremy Thomas on Unsplash