Updated May 12, 2023
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 in any place within that statement multiple times. Generally, the ‘WITH’ clause is used to define CTE. The common table expressions do not exist as a separate statements. Instead, you must always use it along with SELECT, INSERT, UPDATE, or DELETE statements, which immediately follow the CTE. A CTE is similar in function and scope to a derived table in SQL.
Syntax of MySQL CTE
As mentioned earlier, users can use a Common Table Expression in SELECT, INSERT, UPDATE, or DELETE statements. The simple syntax of defining CTE will be:
with cte_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 database. 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 does CTE work 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 the Middle East region from the Asian continent. So, we must consider two conditions: 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;In the first part of the query, users define the CTE. The CTE name for this query is Asian_countries, which holds the list of countries from the 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 with Region = ‘Middle East’, in the CTE Asian_countries.
Output:
Suppose we have the scenario to select specific columns from this example. In the above query, we set 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. You should create the temporary table or common table expression to hold data from the table COUNTRY with the condition Continent = ‘Asia’. Such details as country code, country name, region, and the continent will be stored in the common table expression Asian_countries for those countries with Asia. The common expression “Asian_countries” will store a continent from the “COUNTRY” table.
In the second part of the query, users are instructed to select the country_code and country_name of countries with a Region value of ‘Middle East’ from the Common Table Expression, sort them in ascending order based on the country_name, and display the result. The expected output is a list with columns for Code and Name, arranged in alphabetical order based on the Name column. The list should include only countries from the Asian continent and the Middle East region.
Output:
There are specific contexts to which the use of the WITH clause is restricted. Below are the scenarios:
- Users typically use a clause 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 …- Users cannot use the WITH statement multiple times at the same level. Instead, they should use a single WITH clause and add multiple CTEs, 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 …Users should note that they can reference the same CTE 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 the 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 follows.
- 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 updated by 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 remember 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 the sub-query. People mainly use Recursive CTEs to initiate series like the Fibonacci series, search, or traverse through any data.
Conclusion
MySQL added Common Table Expressions or CTEs to reduce the complexity of SQL queries. CTEs generate a temporary result set that users can access within the same SQL query as a reference or to act upon. The WITH clause mainly defines CTEs, and users can define multiple CTEs in the same WITH clause.
Recommended Articles
We hope that this EDUCBA information on “MySQL CTE” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
