MYSQL CTE

Introduction

In MySQL, every statement or query generates a temporary result or relation. A CTE, or common table expression, is used to identify the temporary results set that occurs within the execution scope of a specific statement, such as CREATE, INSERT, SELECT, UPDATE, DELETE, and so on.

Some of the important points about CTE are as follows:

• The WITH clause is used to define it.
• The WITH clause enables us to specify several CTEs in a single query.
• A CTE can refer to other CTEs in the same WITH clause, but those CTEs must be defined first.
• CTE's execution scope is limited to the statement in which it is utilized.

Syntax Of MySQL CTE

A CTE's structure consists of the name, an optional column list, and a query that specifies the CTE. Once the CTE has been defined, it can be used as a view in a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW query.

The basic syntax of a CTE is illustrated below:

WITH name_of_cte (column_list) AS(
        query 
)
SELECT * FROM name_of_cte; 

It is important to note that the number of columns in the query must match the number of columns in the column_list. If the column list is not specified, the CTE will utilize the column_list of the query that defines the CTE.

MySQL Recursive CTE 

A CTE, as opposed to a derived table, is a subquery that can be self-referencing by using its own name. It is sometimes referred to as recursive CTE and can be used numerous times in the same query.

Some key points about the recursive CTE are as follows:

• The WITH RECURSIVE clause is used to define it.
• A terminating condition must be present in a recursive CTE.
• The recursive CTE will be used for series generation and traversal of hierarchical or tree-structured data.

Syntax Of Recursive CTE

The basic syntax of recursive CTE in MySQL is as follows:

WITH RECURSIVE name_of_cte (name_of_column)AS(
        Query(anchor member) 
        UNION ALL
        Query(recursive member) 
)
SELECT * FROM name_of_cte; 

MySQL CTE Example

Now assumes we need to create a table in the database of an employee's salary - 

Create table salary_table(
ID INT, 
NAME CHAR(20), 
SALARY INT
);

We will have to feed the data in the table using the following command-

INSERT INTO salary_table values
(51,’Ram’,50000),
(52,’ Shyam’,60000)

Use of CTE has been shown below-

WITH 
table1 AS (SELECT ID, NAME, SALARY FROM salary_table)
SELECT * FROM table1;

So this is how we can create a CTE table temporarily for use. 

MySQL Recursive CTE Example

Now the following query demonstrates the usage of the Recursive WITH statement-

WITH RECURSIVE tab AS(
SELECT SALARY FROM salary_table
UNION ALL
SELECT SALARY FROM salary_table WHERE SALARY > 55000
)
SELECT * FROM tab;

You must use the UNION or UNION ALL command to connect the anchor and recursive members. The recursive member, as the name implies, is the recursive part of CTE that will refer to the CTE itself.

In the above code, In the recursive member, we are selecting the column – Salary from the table salary_table where the column Salary is greater than 55000. 

So this is how we can use recursive CTE in MySQL. 

CTE From Multiple Tables

You can also combine many tables to create a Common Table Expression.

Example:

First of all we created a table of students. In this table, we are going to give three columns that are ID, NAME, and AGE. 

CREATE TABLE STUDENT(
ID INT NOT NULL, 
NAME CHAR(30) NOT NULL, 
AGE INT
);

Now we will feed some data into the tables. 

INSERT INTO STUDENT VALUES
(51,’ Ram”,23),
(52,’ Mayank’,45);

The result is as follows. 

Now, we will create another table and fill it as shown below-

CREATE TABLE MARKS(
ID INT NOT NULL, 
MATH INT NOT NULL, 
PHYSICS INT NOT NULL,
CHEMISTRY INT NOT NULL
);

INSERT INTO MARKS VALUES
(51,8,7,9),
(52,6,7,5);

From the code below we can get CTE using two tables combined. 

Input:

WITH 
table1 AS (SELECT ID, NAME FROM STUDENT),
table2 AS (SELECT MATH, PHYSICS,CHEMISTRY FROM MARKS)
SELECT * FROM table1 JOIN table2;

As a result, we will get a table(CTE) from the Command Line Client that is shown below -

We have Created two CTE (table1 and table2) and then we have created a CTE from these two hence this merges the CTE from two tables.

Source: TikTok