Introduction to MySQL 8.0 Common Table Expressions (Part 1)

MySQL Common Table ExpressionsThis 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:

Let’s rewrite it using the CTE instead:

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:

CTE can also be used as the data source for updating other tables as in the following examples:

CTE can also be used for INSERT … SELECT queries like the following: