How to Join 3 Tables in MySQL

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

Businesses and organizations might have to visualize three tables simultaneously based on a particular matching column common to all three tables. This operation is allowed in MySQL with the help of joins.

We can fetch columns as per our requirement from different tables and join the tables based on a particular column that is common to all. For example, we have three tables named table_1, table_2, and table_3.

The first table has the name, the second one has the surname, and the last one has the address. Each one has a primary id to merge or visualize these tables as one using the common primary id MySQL.

Let us understand how this method works. But before we begin, we must create three dummy datasets by creating a table, student_details, along with 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 view 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 table named student_marks containing the marks of each student corresponding to the stu_id. We can make 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

Lastly, let us create a third table named student_email. This table would possess the stu_id and stu_email columns. The stu_id column would be common to all three tables, whereas the stu_email column would represent the student’s email address under consideration.

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 the student_email table created above with 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 to get three values, particularly the student first name, marks, and email address, from the three tables created above with the help of the common column stu_id.

Join 3 Tables in MySQL

To merge our three tables, we can use the common column and fetch different columns from separate 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 seen in the query above, we are joining three tables based on the common student identity. The output of the code above 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

Therefore, with the help of the WHERE and ON clauses, we can efficiently join three different tables and visualize their columns as one based on a common condition in MySQL.