MySQL Common Table Expression (CTE)

If your interested to learn about the MySQL Lead and Lag function

In MySQL, every statement or query produces a temporary result or relation. A common table expression or CTE is used to name those temporary results set that exist within the execution scope of that particular statement, such as CREATE, INSERT, SELECT, UPDATE, DELETE, etc. Some of the key point related to CTE are:

• It is defined by using the WITH clause.
• The WITH clause allows us to specify more than one CTEs in a single query.
• A CTE can reference other CTEs that are part of the same WITH clause, but those CTEs should be defined earlier.
• The execution scope of CTE exists within the particular statement in which it is used.

MySQL CTE Syntax

The syntax of MySQL CTE includes the name, an optional column list, and a statement/query that defines the common table expression (CTE). After defining the CTE, we can use it as a view in a SELECT, INSERT, UPDATE, and DELETE query.

The following is the basic syntax of CTE in MySQL:

WITH cte_name (column_names) AS (query)   
SELECT * FROM cte_name;  

It is to ensure that the number of columns in the CTE arguments must be the same as the number of columns in the query. If we have not defined the columns in the CTE arguments, it will use the query columns that define the CTE. Similar to the derived table, it cannot be stored as an object and will be lost as soon as the execution of a query completed. A CTE provides better readability and also increases the performance as compared to the derived table. Unlike a derived table, a CTE is a subquery that can be self-referencing using its own name. It is also known as recursive CTE and can also be referenced multiple times in the same query. Some of the essential points related to the recursive CTE are:

• It is defined by using the WITH RECURSIVE clause.
• A recursive CTE must contain a terminating condition.
• We will use the recursive CTE for series generation and traversal of hierarchical or tree-structured data.

Why would you use a common table expression CTE MySQL?

In MySQL, every statement or query produces a temporary result or relation. A common table expression or CTE is used to name those temporary results set that exist within the execution scope of that particular statement, such as CREATE, INSERT, SELECT, UPDATE, DELETE, etc.

MySQL Recursive CTE Syntax

The following is the basic syntax of recursive CTE in MySQL:

WITH RECURSIVE cte_name (column_names) AS ( subquery )   
SELECT * FROM cte_name;  

Here, the subquery is a MySQL query refer itself by using the cte_name as its own name.

MySQL CTE Examples

Let us understand how CTE works in MySQL using various examples. Here, we are going to use a table “employees” for a demonstration. Suppose this table contains the following data:

Execute the following statement to understand the concept of CTE. In this example, the CTE name is employee_in_california, the subquery that defines the CTE returns the three columns emp_name, emp_age, and city. Hence, the CTE employee_in_california will return all employees who are located in the California city. After defining the CTE employee_in_california, we have referenced it in the SELECT statement for selecting only those employees who are located in California.

WITH employees_in_california AS (  
    SELECT * FROM employees WHERE city = 'California'   
    )   
    SELECT emp_name, emp_age, city FROM employees_in_california  
    WHERE emp_age >= 32 ORDER BY emp_name;  

After executing the above statement, it will give the following output. Here, we can see that the result returns only the employee data that are located in California.

The more advanced MySQL CTE example

Suppose we have a table named customer and order that contains the following data:

Table: customer

Table: orders

See the below statement that explains advanced CTE example using the INNER JOIN clause.

WITH total_customer_2020 AS (  
    SELECT cust_id, name, occupation FROM customer   
    INNER JOIN orders USING (cust_id)  
    ORDER BY age  
)  
SELECT * FROM orders JOIN total_customer_2020 USING (cust_id);  

After executing, we will get the output as below:

MySQL Recursive CTE Example

The following examples explain the working of the recursive CTE. Consider the below statement which generates a series of first five odd numbers:

WITH RECURSIVE   
odd_num_cte (id, n) AS  
(  
SELECT 1, 1   
union all  
SELECT id+1, n+2 from odd_num_cte where id < 5   
)  
SELECT * FROM odd_num_cte;  

After executing the above statement, it will give the output as below:

The above statement consists of two parts one is non-recursive, and another is recursive.

Non-recursive: SELECT 1, 1

This part will produce the initial rows with two columns as “id” and “n” and a single row.

Recursive: SELECT id+1, n+2 from odd_num_cte where id < 5

This part is responsible for adding rows to the previous output until the terminating condition (id < 5) will not be satisfied. When the id reached 5, the condition becomes false, and the recursion process is terminated.

The WITH clause uses

MySQL provides many contexts to use the WITH clause for creating CTE. Let’s discuss one by one in detail. First, we can use the WITH clause at the beginning of SELECT, UPDATE, and DELETE query as below.

WITH ... SELECT ...  
WITH ... UPDATE ...  
WITH ... DELETE ...  

Second, we can use the WITH clause at the beginning of a subquery or a derived table subquery as below:

SELECT ... WHERE id IN (WITH ... SELECT ...);  
  
SELECT * FROM (WITH ... SELECT ...) AS derived_table;  

Third, we can use the WITH clause immediately preceding of SELECT statements that include a SELECT clause as below:

CREATE TABLE ... WITH ... SELECT ...  
CREATE VIEW ... WITH ... SELECT ...  
INSERT ... WITH ... SELECT ...  
REPLACE ... WITH ... SELECT ...  
DECLARE CURSOR ... WITH ... SELECT ...  
EXPLAIN ... WITH ... SELECT ...  

Benefits of using CTE

• It provides better readability of the query.
• It increases the performance of the query.
• The CTE allows us to use it as an alternative to the VIEW concept
• It can also be used as chaining of CTE for simplifying the query.
• It can also be used to implement recursive queries easily.

Query:

<code>WITH RECURSIVE trial (count) AS<br>(<br>SELECT 100<br>UNION ALL<br>SELECT count + 50 FROM trial WHERE count &lt; 500<br>)<br>SELECT * FROM trial;</code>

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.