MySQL – Common Table Expressions


MySQL – Common Table Expression (WITH)



”;


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, ''[email protected]'', ''Hyderabad''),
(102, ''[email protected]'', ''Vishakhapatnam'');

The CONTACT table produced is as follows −





ID EMAIL CITY
101 [email protected] Hyderabad
102 [email protected] 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 [email protected] NULL
101 Serena Williams [email protected] NULL
102 Virat Kohli [email protected] NULL
101 Serena Williams [email protected] NULL

Advertisements

”;

Leave a Reply

Your email address will not be published. Required fields are marked *