MySQL Common Table Expression (CTE)

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:

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.

MySQL Recursive CTE Syntax

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

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.

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.

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:

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.

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

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

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.