JIYIK CN >

This article aims to understand how to use common table expressions in MySQL.

Most data analysts need to store the results of different queries in order to merge them with a separate query. With the help of common tables, expressions can make this possible. These are sometimes also called WITHclauses.

Let us try to understand this a little deeper.

However, before we start, we create two virtual tables to work in. Here, we create a table student_datesand a few rows.

-- create the table student_dates
CREATE TABLE student_dates(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_date date,
  primary key(stu_id)
);

Similarly, we can create a table student_detailsand some defined rows using the following query.

-- create the table student_details
CREATE TABLE student_details(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);

Use INSERTthe statement to insert entries into the student_datesand student_detailstables

student_datesCreate a table with that name to create a table with that name.

Now with INSERTthe help of statement, let us try to add data for some students. This can be done as follows:

-- insert rows to the table student_dates
INSERT INTO student_dates(stu_firstName,stu_date) 
 VALUES("Preet",STR_TO_DATE('24-May-2005', '%d-%M-%Y')),
 ("Dhruv",STR_TO_DATE('14-June-2001', '%d-%M-%Y')),
 ("Mathew",STR_TO_DATE('13-December-2020', '%d-%M-%Y')),
 ("Jeet",STR_TO_DATE('14-May-2003', '%d-%M-%Y')),
 ("Steyn",STR_TO_DATE('19-July-2002', '%d-%M-%Y'));

The above code can be used student_datesto input student data in . The following command can be used to visualize this table using:

SELECT * from student_dates;

The above code block will generate the following output:

stu_id	stu_firstName	stu_date
1		Preet			2005-05-24
2		Dhruv			2001-06-14
3		Mathew			2020-12-13
4		Jeet			2003-05-14
5		Steyn			2002-07-19

Similarly, let us student_detailsinsert values ​​into the table which can be done with the help of the following query.

-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName) 
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

student_detailsThe table can be visualized with the help of the following query.

SELECT * from student_details;

Output:

stu_id	stu_firstName	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

Common table expressions in MySQL

Now let us try to understand WITHthe clause.

stu_idWe can use this clause to join the two tables and get the name and date of the students. The two tables can be matched with the help of which acts as the primary key .

You can do this using the following query.

WITH
  cte1 AS (SELECT stu_id, stu_firstName FROM student_details),
  cte2 AS (SELECT stu_id, stu_date FROM student_dates)
SELECT stu_firstName, stu_date FROM cte1 JOIN cte2
WHERE cte1.stu_id = cte2.stu_id;

The previous query will give us the following output.

stu_firstName	stu_date
Preet			2005-05-24
Rich			2001-06-14
Veron			2020-12-13
Geo				2003-05-14
Hash			2002-07-19

From the above code block, stu_firstNamethe columns are matched stu_idwith the related with the help of column stu_date.

Thus, with WITHthe help of the clause, we can effectively write common table expressions to store the query in a specific variable for later use in MySQL.