SQL Join 3 Tables – Join Query for Three Tables

In this tutorial, We will discuss SQL JOIN 3 tables with the best example. with this example, you will definitely become an expert in joining three or more tables. Even you will get the standard SQL join query for 3 tables that will be very useful to join the largest & complex tables.

As you know that we have always to write multiple select statements to select data from multiple tables. But It is not good for a large number of records & tables. It may increase the execution time or kill the queries. So, we need to write a single SELECT statement with SQL JOIN.

How to JOIN 3 tables in SQL? This is the most popular question that is frequently asked in the interview. So. It is very important for you.

If you don’t have basic knowledge of SQL JOIN, you need not worry about it. Here I will explain everything related to it.

Join 3 Tables in SQL

SQL JOIN has four joining keywords like INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN. So, You will learn to join 3 tables using these keywords from the next step.

Important Points

To join 3 tables, You should know the following points –

• All the 3 tables should be in a relationship with a foreign key.
• Each table must have a common column.
• The common column must contain the matching values.
• a common column of the first tables must have a primary key and another common column of the second tables must have a foreign key.
• datatype & name of the common column may be the same or different.
• All 3 tables contain the same or the different numbers of rows or columns with some values.

Example –

To understand SQL join Query for 3 tables. You have to see an example. So, Let’s discuss it with the three tables like students, courses, subjects. Each table is related to each other with the Foreign keys.

Now, See the structure of the following tables.

Table Name – universities

Table Name – students

This table will be joined to universities table based on a common column  university_id.

Table Name – contacts

This table will be joined to students table based on a common column  student_id

INNER JOIN 3 Tables

If you use the INNER JOIN keyword with the select statement, It will return a new result table by joining the above three tables with the matching records of each table

Syntax –

You can join 3 tables using the following INNER JOIN syntax –

SELECT table1.column1_name, table1.column2_name,..., 
       table2.column1_name, table2.column2_name,..., 
       table3.column1_name, table3.column2_name,..., 
FROM table1
INNER JOIN table2 ON table1.table1_id = table2.table1_id 
INNER JOIN table3 ON table2.table2_id  = table3.table2_id;

Query –

SELECT students.full_name, students.gender, contacts.mobile_number, contacts.email_address, universities.university_name
FROM universities
INNER JOIN students ON universities.university_id = students.university_id
INNER JOIN contacts ON students.student_id = contacts.student_id;

Output –

LEFT JOIN 3 Tables

If you use the LEFT JOIN keyword with the select statement, It will return a new result table by joining the above three tables with all the records of left the table and matching values of the right tables.

Syntax –

You can join 3 tables using the following INNER JOIN syntax –

SELECT table1.column1_name, table1.column2_name,..., 
       table2.column1_name, table2.column2_name,..., 
       table3.column1_name, table3.column2_name,..., 
FROM table1
LEFT JOIN table2 ON table1.table1_id = table2.table1_id 
LEFT JOIN table3 ON table2.table2_id  = table3.table2_id;

Query –

SELECT students.full_name, students.gender, contacts.mobile_number, contacts.email_address, universities.university_name
FROM universities
LEFT JOIN students ON universities.university_id = students.university_id
LEFT JOIN contacts ON students.student_id = contacts.student_id;

Output –

RIGHT JOIN 3 Tables

If you use the RIGHT JOIN keyword with the select statement, It will return a new result table by joining the above three tables with all the records of right the table and matching values of the left tables.

Syntax –

You can join 3 tables using the following INNER JOIN syntax –

SELECT table1.column1_name, table1.column2_name,..., 
       table2.column1_name, table2.column2_name,..., 
       table3.column1_name, table3.column2_name,..., 
FROM table1
RIGHT JOIN table2 ON table1.table1_id = table2.table1_id 
RIGHT JOIN table3 ON table2.table2_id  = table3.table2_id;

Query –

SELECT students.full_name, students.gender, contacts.mobile_number, contacts.email_address, universities.university_name
FROM universities
RIGHT JOIN students ON universities.university_id = students.university_id
RIGHT JOIN contacts ON students.student_id = contacts.student_id;

Output –

FULL JOIN 3 Tables

If you use the RIGHT JOIN keyword with the select statement, It will return a new result table by joining the above three tables with all the records of right the table and left tables.

Syntax –

You can join 3 tables using the following INNER JOIN syntax –

SELECT table1.column1_name, table1.column2_name,..., 
       table2.column1_name, table2.column2_name,..., 
       table3.column1_name, table3.column2_name,..., 
FROM table1
FULL JOIN table2 ON table1.table1_id = table2.table2_id 
FULL JOIN table3 ON table2.table2_id  = table3.table2_id;

Query –

SELECT students.full_name, students.gender, contacts.mobile_number, contacts.email_address, universities.university_name
FROM universities
FULL JOIN students ON universities.university_id = students.university_id
FULL JOIN contacts ON students.student_id = contacts.student_id;

Output –

Join 3 Tables with Conditions

Here, you will learn to join 3 tables with different conditions using the INNER JOIN keyword. Once you learn it, you will definitely know to use LEFT JOIN, RIGHT JOIN & FULL JOIN keyword.

Join 3 Tables with WHERE Clause

If you want to select those records that have a gender value is ‘male’ then you can join 3 tables with where clause.

SELECT students.full_name, students.gender, contacts.mobile_number, contacts.email_address, universities.university_name
FROM universities
LEFT JOIN students ON universities.university_id = students.university_id
LEFT JOIN contacts ON students.student_id = contacts.student_id
WHERE students.gender='male';

Join 3 Tables with ORDER BY Clause

If you want to select records based on full_name in alphabetical order then you can join 3 tables with ORDER BY clause.

SELECT students.full_name, students.gender, contacts.mobile_number, contacts.email_address, universities.university_name
FROM universities
LEFT JOIN students ON universities.university_id = students.university_id
LEFT JOIN contacts ON students.student_id = contacts.student_id
ORDER BY students.full_name;

Join 3 Tables with GROUP BY clause

If you want to count records by grouping gender then you can join 3 tables with the GROUP  BY clause

SELECT students.full_name, students.gender, contacts.mobile_number, contacts.email_address, universities.university_name
FROM universities
LEFT JOIN students ON universities.university_id = students.university_id
LEFT JOIN contacts ON students.student_id = contacts.student_id
GROUP BY students.gender;

How to Join 3 Tables in SQL

To join 3 tables, you have to configure the following points –

• First of all, make sure that the SQL package is installed in your computer
• Create a MySQL Database
• Create 3 tables in MySQL database
• Insert some records in all three tables
• Join all three 3 tables using INNER JOIN, LEFT JOIN, RIGHT JOIN, or FULL JOIN.

Query –

Using INNER JOIN  Command

Run the following command to join all three tables using INNER JOIN.

mysql>CREATE DATABASE codingstatus;
mysql> USE DATABASE codingstatus;
mysql>CREATE TABLE universities
      (
      university_name varchar(200),
      university_code int(20)
      );
mysql>INSERT INTO universities(university_id, university_name)
      VALUES(101, 'SRM University'),
      (1002, 'Amity University'),
      (1003, 'Anna University'),
      (1004, 'Hindustan University');
      
mysql>SELECT * FROM universities;
mysql>CREATE TABLE students
      (
      student_id int(10),
      full_name varchar(20),
      gender varchar (10),
      university_id int,
      CONSTRAINT FK_university_id FOREIGN KEY (university_id)
      REFERENCES universities(university_id)
      );
mysql>INSERT INTO students(student_id, full_name, gender, university_id) VALUES     
      (201,'Noor Khan', 'Male', 1001),
      (202, 'Avneesh Mishra', 'Male', 1002),
      (203,'Monika Singh', 'Female', 1003),
      (204, 'Aaliya Khan', 'Female', 1004),
      (205, 'Sunil Kumar', 'Male', 1004),
      (206, 'Mamta Gupta', 'Female', 1004),    
      (207, 'Rapson Jani', 'Male', 1001),
      (208, 'Kundan Bharti', 'Male', 1001),
      (209, 'Manmohan Singh', 'Male', 1009),
      (210,'Manisha Chaudhry', 'Female', 1010);
mysql>SELECT * FROM students;
mysql>CREATE TABLE contacts
      (
      contact_id int(10),
      mobile_number int(20),
      email_address varchar (50),
      student_id int,
      CONSTRAINT FK_student_id FOREIGN KEY (student_id)
      REFERENCES students(student_id)
      
      );
mysql>INSERT INTO contacts(contact_id, mobile_number, email_address, student_id) VALUES     
      (51, 2222222222, 'aaaa222@gmail.com', 201),
      (52, 5555555555, 'bbbb555@gmail.com', 202),
      (53, 7777777777, 'cccc666@gmail.com', 203),
      (54, 8888888888, 'dddd888@gmail.com', 204),
      (55, 1010101010, 'eeee101@gmail.com', 204),
      (56, 1212121212, 'ffff121@gmail.com', 204),    
      (57, 1313131313, 'gggg121@gmail.com', 202),
      (58, 1414141414, 'iiii121@gmail.com', 202),
      (59, 1515151515, 'jjjj151@gmail.com', 2015);
mysql>SELECT * FROM contacts;
mysql>SELECT students.full_name, students.gender, contacts.mobile_number, contacts.email_address,                                                    iversities.university_name
      FROM universities
      INNER JOIN students ON universities.university_id = students.university_id
      INNER JOIN contacts ON students.student_id = contacts.student_id;

Note – You have created a database & tables in the previous step. Now, you need not create it again.

Using LEFT JOIN Command 

Run the following command to join all three tables using LEFT JOIN.

mysql>SELECT students.full_name, students.gender, contacts.mobile_number, contacts.email_address, iversities.university_name
      FROM universities
      LEFT JOIN students ON universities.university_id = students.university_id
      LEFT JOIN contacts ON students.student_id = contacts.student_id;

Using Right JOIN Command 

Run the following command to join all three tables using the RIGHT JOIN.

mysql>SELECT students.full_name, students.gender, contacts.mobile_number, contacts.email_address, iversities.university_name
      FROM universities
      RIGHT JOIN students ON universities.university_id = students.university_id
      RIGHT JOIN contacts ON students.student_id = contacts.student_id;

Using FULL JOIN Command

Run the following command to join all three tables using the RIGHT JOIN.

mysql>SELECT students.full_name, students.gender, contacts.mobile_number, contacts.email_address, iversities.university_name
      FROM universities
      FULL JOIN students ON universities.university_id = students.university_id
      FULL JOIN contacts ON students.student_id = contacts.student_id;

 

Tutorial Summary

Dear Developer, I have this tutorial with complete information. I hope that You have learned to join 3 tables using SQL. Now you can join any type of table.

If you have any questions, Kindly ask me through the comment box. Even, you should share this tutorial with your friends.