Common Table Expressions in MySQL
-
Inserting Entries in the
student_dates
and thestudent_details
Tables Using theINSERT
Statement - 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.