Common Table Expressions in MySQL

1. Inserting Entries in the student_dates and the student_details Tables Using the INSERT Statement
2. Common Table Expressions in MySQL

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

Most data analysts need to store the results of different queries to merge them with separate ones. With the help of a common table, expressions can be made possible. These are also sometimes referred to as the WITH clause.

Let us try to understand this in greater depth.

However, before we begin, we create two dummy tables to work. Here we create a table, student_dates, along with 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 the table student_details and a few defined rows with 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)
);

Inserting Entries in the student_dates and the student_details Tables Using the INSERT Statement

The student_dates creates a table with the name makes a table with the name.

Now with the help of the INSERT statement, let us try to add data for a few students. This operation 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 code above can enter the student data in student_dates. the following command can visualize this table with:

SELECT * from student_dates;

The above stated code block would 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 insert values in the student_details table can do this 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");

The student_details 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 the WITH clause.

We can use this clause to merge the two tables and fetch the students’ first names and dates. These two tables can be matched with the help of the stu_id that acts as the primary key.

This operation can be performed with the use of 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 would 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 code blocks above, the stu_firstName column is matched with the relevant stu_date with the help of the stu_id column.

Thus with the help of the WITH clause, we can efficiently write common table expressions to store a query in a particular variable that can be used later in MySQL.