The MySQL Common Table Expression

The MySQL Common Table Expression (CTE) is a temporary result-set or table that exists for the duration of a single query. We can use the CTEs to reference the result-set within the context of a single SELECT, UPDATE, INSERT, DELETE, CREATE, VIEW, or MERGE statement.

The scope of a CTE is limited to that specific query. It makes complex queries simple by breaking them into simple blocks.

The MySQL WITH Clause

The MySQL WITH clause is used to create CTEs by having one or more comma-separated subclauses. The subclauses provide a subquery that generates the result-set.

WITH clause cannot be used in MySQL versions before 8.0.

Syntax

Following is the syntax of the MySQL WITH clause −

WITH name_for_summary_data AS (SELECT Statement)
SELECT columns
FROM name_for_summary_data
WHERE conditions  (
   SELECT column
   FROM name_for_summary_data
)
[ORDER BY columns]

Example

Assume we have created a table named DATA containing details such as id, name, age and salary −

CREATE TABLE DATA(
   ID INT,
   NAME CHAR(20),
   AGE INT,
   SALARY INT
);

Now, let us insert values into the table created above using the INSERT INTO statement as shown below −

INSERT INTO DATA VALUES 
(101, ''John'', 25, 55452),
(102, ''Jane'', 29, 66458),
(103, ''Arub'', 35, 36944);

In the following example, the WITH clause is used to create a CTE named CTE, which is then queried to retrieve the data from the DATA table −

WITH CTE AS 
(Select ID, NAME, AGE, SALARY FROM DATA)
SELECT * FROM CTE;

After executing the above code, we get the output as follows −

ID	NAME	AGE	SALARY
101	John	25	55452
102	Jane	29	66458
103	Arub	35	36944

CTE from Multiple Tables

We can create CTEs from multiple tables by separating each CTE subclause using a comma (”,”).

Example

Suppose we have created a table with name EMPLOYEE and populated data into it as shown below −

CREATE TABLE EMPLOYEE(
   ID INT NOT NULL,
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT,
   CONTACT INT
);

Here, we are inserting records into the EMPLOYEE table −

INSERT INTO EMPLOYEE VALUES
(101, ''Serena'', ''Williams'', 27, ''F'', 9000, 101),
(102, ''Virat'', ''Kohli'', 20, ''M'', 6000, 102);

The EMPLOYEE table obtained is as follows −

ID	FIRST_NAME	LAST_NAME	AGE	SEX	INCOME	CONTACT
101	Serena	Williams	27	F	9000	101
102	Virat	Kohli	20	M	6000	102

Now, we create another table CONTACT −

CREATE TABLE CONTACT(
   ID INT NOT NULL,
   EMAIL CHAR(20) NOT NULL,
   PHONE LONG,
   CITY CHAR(20)
);

Let us insert some records in the CONTACT table −

INSERT INTO CONTACT (ID, EMAIL, CITY) VALUES
(101, ''serena@mymail.com'', ''Hyderabad''),
(102, ''virat@mymail.com'', ''Vishakhapatnam'');

The CONTACT table produced is as follows −

ID	EMAIL	CITY
101	serena@mymail.com	Hyderabad
102	virat@mymail.com	Vishakhapatnam

Following example uses Common Table Expressions (CTEs) named “exp1” and “exp2” to separately select specific columns from the EMPLOYEE and CONTACT tables. The final SELECT statement joins these CTEs, combining the chosen columns from each −

WITH
exp1 AS (SELECT ID, FIRST_NAME, LAST_NAME FROM EMPLOYEE),
exp2 AS (SELECT EMAIL, PHONE FROM CONTACT)
SELECT * FROM exp1 JOIN exp2;

Following is the output of the above code −

ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE
102	Virat	Kohli	serena@mymail.com	NULL
101	Serena	Williams	serena@mymail.com	NULL
102	Virat	Kohli	virat@mymail.com	NULL
101	Serena	Williams	virat@mymail.com	NULL