MySQL CTE

Definition of MySQL CTE

CTE stands for Common Table Expressions. In MySQL, it is a temporary result set that has a range within a single SQL statement. A CTE can be referenced any place within that statement multiple times. Generally, the ‘WITH’ clause is used to define a CTE. The common table expressions do not exist as a separate statement, instead, it is always used along with SELECT, INSERT, UPDATE, or DELETE statements, which immediately follows the CTE. A CTE is similar in function and scope to a derived table in SQL.

Syntax of MySQL CTE

As mentioned, a common table expression is used in the SELECT, INSERT UPDATE, or DELETE statements. The simple syntax of defining CTE will be:

withcte_name (column_names) as (
query )

Here the cte_name is the name of the common table expression. The cte_name is followed by the columns to be selected. Further, the condition to select the columns is defined within the braces. So, now we will try to put this in our table and discuss further details of common table expressions.

We will be considering the Country table from the sample data base. We have 239 countries listed in this table. The below given screenshot is a part of the table. Let’s try to write a query with CTE from this big table for easy access to a defined temporary data set.

select * from country order by Continent;

How CTE Works in MySQL?

Let’s first look at one simple SELECT query with the CTE defined in it. Our scenario is to identify countries belonging to Middle East region from Asian continent. So, we have to consider two condition, which are firstly Continent = ‘Asia’ and then Region = ‘Middle East’. For this scenario, the query will be as below

Query:

WITH Asian_countries as (
SELECT *
FROM country
WHERE Continent = 'Asia' )
SELECT *
FROM Asian_countries
WHERE Region = 'Middle East'
ORDER by Name;

The CTE is defined in the first part of the query. The CTE name for this query isAsian_countries, which holdsthe list of countries from original table COUNTRY with the condition as Continent =’Asia’. And in the second part of the query, we are picking up the details of countries which has Region = ‘Middle East’, in the CTE Asian_countries.

Output:

Now suppose we have the scenario to select specific columns from this same example. In the above query, we selected all columns in the table.

Query:

WITH Asian_countries as (
SELECT Code, Name, Continent , Region
FROM country
WHERE Continent = 'Asia' )
SELECT Code, Name
FROM Asian_countries
WHERE Region = 'Middle East'
ORDER by Name;

Here the CTE is Asian_countries. So the temporary table or common table expression is supposed to have data from the table COUNTRY with the condition Continent = ‘Asia’. So details like country code, country name, region, and continent, for those countries with Asia as a continent from table COUNTRY will be stored in the common table expression Asian_countries.

The second part of the query says, from this CTE, the country_code and country_name of those countries with Region = ‘Middle East’ isto be picked up, arranged in ascending order of countr_name and displayed. The expected output will be a list with Code and Name as columns, arranged in alphabetical order of Name which belongs to the Asian continent and Middle East region.

Output:

There are certain contexts to which the use of the WITH clause is restricted. Below are the scenarios:

• A clause is used at the beginning of SELECT, UPDATE, and DELETE statements.

WITH … SELECT …
WITH … UPDATE …
WITH … DELETE …

• In statements that include a SELECT statement, preceding the SELECT statement.

INSERT … WITH … SELECT …
REPLACE … WITH … SELECT …
CREATE TABLE …WITH … SELECT …

• WITH statement cannot be used multiple times at same level. Instead, a single WITH clause is used and the multiple CTE are added separated by a comma.

Incorrect usage:

WITH cte_name1 as (….), WITH cte_name2 as (….) SELECT ….

Correct usage:

WITH cte_name1 as (….), cte_name2 as (….) SELECT …

Note the fact that the same CTE can be referenced within another CTE or by itself. When a CTE refers to itself, it becomes a recursive CTE. Let’s try to understand a simple recursive CTE with below example:

Query:

WITH RECURSIVE trial (count) AS
(
SELECT 100
UNION ALL
SELECT count + 50 FROM trial WHERE count < 500
)
SELECT * FROM trial;

The CTE name in this query is ‘trial’. And we have three parts in the CTE sub-query as following.

• SELECT … : this part initializes the first row and is a non-recursive part of the query.
• UNION ALL: This part separates the non-recursive and recursive parts of the sub-query. It can be either UNION ALL or UNION DISTINCT.
• SELECT …: This part displays the further rows and is the recursive part of the sub-query. The example has this part as -SELECT count + 50 FROM trial WHERE count < 500- where the value ‘count’ is to be update by a value of 50 until it reaches the value 500.

Let’s have a look at the output of this query.

Output:

We can see that the output has a single column ‘count’ with values from 100 to 500, each updated by 50. We need to keep in mind that, both the recursive and non-recursive portions of the sub-query must have the same column widths for all columns because the output column width is dependent upon the non-recursive part of sub-query. Recursive CTEs are mainly used to initiate series like Fibonacci series, search or traverse through any data.

Conclusion

Common table expressions or CTEs were added to MySQL, to reduce the complexity of SQL queries. It generates a temporary result set, which can be accessed within that same SQL query, as a reference or for being acted upon. To define CTE, the WITH clause is mainly used and multiple CTE can be defined in the same WITH clause.

Recommended Articles

This is a guide to MySQL CTE. Here we also discuss the definition and syntax of mysql cte? along with how cte works in mysql?. You may also have a look at the following articles to learn more –

1. MySQL IN Operator
2. Unique Key in MySQL
3. MySQL Subquery
4. MySQL Constraints