JIYIK CN >

In this tutorial, we will learn how to join three tables in MySQL.

Businesses and organizations may have to visualize three tables simultaneously based on certain matching columns common to all three tables. This operation is allowed in MySQL with the help of joins.

We can fetch columns from different tables based on our requirement and join the tables based on all common specific columns. For example, we have three tables named table_1, , table_2and table_3.

The first table has first names, the second has last names, and the last has addresses. Each table has a primary ID, and using the common primary ID MySQL can merge or visualize these tables into one table.

Let's understand how this approach works. But before we start, we have to student_detailscreate three dummy datasets by creating a table and a few rows.

-- 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)
);
-- 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");

To see the entries in the data, we use the following code.

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

Next, we need another student_markstable called that contains stu_idthe marks for each student corresponding to . We can create such a table using the following query.

-- create the table student_details
CREATE TABLE student_marks(
  stu_id int,
  stu_marks int
);
-- insert rows to the table student_details
INSERT INTO student_marks(stu_id,stu_marks) 
 VALUES(1,10),
 (2,20),
 (3,30),
 (4,7),
 (5,9),
 (6,35),
 (7,15);

We can visualize this table using the following query.

SELECT * from student_marks;

Output:

stu_id  stu_marks
1		10
2		20
3		30
4		7
5		9
6		35
7		15

Finally, let's create a student_emailthird table called . This table will have stu_idand stu_emailcolumns. stu_idThe columns will be common to all three tables, while stu_emailthe column will represent the email address of the student being considered.

We can create the third table with the help of the following query.

-- CREATE TABLE student_email
CREATE TABLE student_email(
  stu_id int,
  stu_email varchar(255) DEFAULT NULL
);

-- insert rows to the table student_email
INSERT INTO student_email(stu_id,stu_email) 
 VALUES(1,"abc@d.in"),
 (2,"SEAabc@d.in"),
 (3,"DEabc@d.in"),
 (4,"KARTabc@d.in"),
 (5,"MARIOabc@d.in"),
 (6,"SPETERabc@d.in"),
 (7,"DAVIDabc@d.in");

We can visualize student_emailthe table created above using the following query.

SELECT * from student_email;

Output:

stu_id	stu_email
1		abc@d.in
2		SEAabc@d.in
3		DEabc@d.in
4		KARTabc@d.in
5		MARIOabc@d.in
6		SPETERabc@d.in
7		DAVIDabc@d.in

Let us try stu_idto fetch three values ​​from the three tables created above with the help of common columns, specifically name, marks and email address of the student.

Joining 3 tables in MySQL

To merge our three tables, we can use the common columns and get the different columns from different tables with the help of the following query.

select a.stu_firstName as "Name", b.stu_email as "Email", c.stu_marks as "Marks"
from student_details a, student_email b, student_marks c
where a.stu_id = b.stu_id and b.stu_id = c.stu_id 

As shown in the above query, we are joining three tables based on the common student identity. The output of the above code is as follows.

Name	Email			Marks
Preet	abc@d.in		10
Rich	SEAabc@d.in		20
Veron	DEabc@d.in		30
Geo		KARTabc@d.in	7
Hash	MARIOabc@d.in	9
Sachin	SPETERabc@d.in	35
David	DAVIDabc@d.in	15

Thus, with the help of WHEREAND ONclauses, we can effectively join three different tables and visualize their columns as one based on common conditions in MySQL.