Joining Three Or More Tables In SQL Server 2017

Introduction

 
In this article, I am going to explain how to join three or more tables and also define the joins in SQL Server. If you want to retrieve data from multiple tables then you need to use joins in SQL Server. Here we will be using SQL Server 2017 or you can use SQL Server 2008 or above.
 
Read my previous Joins in SQL Server 2017 part of this article using the below links before reading this article,
Definition
 
It is used to fetch/retrieve data from two or more related tables from the database. In general, tables are related to each other using foreign key constraints.
 

Types of Joins

 
There are four types of joins in SQL Server.
  • Inner Join
  • Outer Join
  • Cross Join
  • Self Join
Again, Outer Joins are divided into three types.
  • Left Join or Left Outer Join
  • Right Join or Right Outer Join
  • Full Join or Full Outer Join
Now, I am going to explain different types of joins with examples and the differences between them.
 
Prerequisites
 
SQL Server 2017 or you can use SQL server 2008 or above version.
 
Now, first we will create a Database and three tables to apply the joins for understanding.
 

Creating Database and Three Tables

 
Step 1 - Create a Database
 
Open your SQL Server and use the following script to create the “chittadb” Database.
  1. Create database chittadb  
Now, select the script query then press F5 or click on Execute button to execute the above script.
 
You should see a message, “Command(s) completed successfully.” This means your new database was created.
 
Step 2 - Create first table
 
Open your SQL Server and use the following script to create table “tbl_Department”.
  1. create table tbl_Department  
  2. (  
  3.    DeptId int primary key ,  
  4.    DeptName nvarchar(50),  
  5.    DeptHead nvarchar(50),  
  6.    Location nvarchar(100)  
  7. )  
Execute the above query to create “tbl_Department “.
 
You should see a message, “Command(s) completed successfully.”
 
Now, data is inserted into the table.
  1. Insert into tbl_Department values (1, 'IT''Chitta''Chennai')  
  2. Insert into tbl_Department values (2, 'Payroll''Akhil''Odisha')  
  3. Insert into tbl_Department values (3, 'HR''Ram''Pune')  
  4. Insert into tbl_Department values (4, 'Timesheet''Kannan''chennai')  
Execute the above query, you should see a message, “Command(s) completed successfully.”
 
Now retrieve all data from “tbl_Department” table.
  1. select * from tbl_Department  
output
 
Joining Three Or More Tables In SQL Server 2017
 
Step 3 - Create second table
 
Open your SQL Server and use the following script to create table “tbl_Gender”.
  1. Create Table tbl_Gender  
  2. (  
  3.      GenderId int primary key,  
  4.      Gender nvarchar(50)  
  5. )  
Execute the above query to create “tbl_Gender “.
 
You should see a message, “Command(s) completed successfully.”
 
Now, data is inserted into the table.
  1. Insert into tbl_Gender values (1, 'Male')  
  2. Insert into tbl_Gender values (2, 'Female')  
Execute the above query, you should see a message, “Command(s) completed successfully.”
 
Now retrieve all data from “tbl_Gender” table.
  1. select * from tbl_Gender  
output
 
Joining Three Or More Tables In SQL Server 2017
 
Step 3 - Create third table
 
Open your SQL Server and use the following script to create table “tbl_Employee”.
  1. Create table tbl_Employee  
  2. (  
  3.      EmpId int primary key ,  
  4.      Name nvarchar(50),  
  5.      country nvarchar(20),  
  6.      Salary int,  
  7.      DepartmentId int foreign key references tbl_Department(DeptId),  
  8.      GenderID int foreign key references tbl_Gender(GenderId)  
  9. )  
Execute the above query to create “tbl_Employee “.
 
You should see a message, “Command(s) completed successfully.”
 
Now, data is inserted into the table.
  1. Insert into tbl_Employee values (1, 'Jitu','India',4000, 1,1)  
  2. Insert into tbl_Employee values (2, 'Rani','India', 5000, 3,2)  
  3. Insert into tbl_Employee values (3, 'Dibas','India', 6500, 2,1)  
  4. Insert into tbl_Employee values (4, 'Gajendra','India', 3800, 2,1)  
  5. Insert into tbl_Employee values (5, 'Raja','India', 9000, 1,1)  
  6. Insert into tbl_Employee values (6, 'Jeni','India', 5800, 3,2)  
  7. Insert into tbl_Employee values (7, 'Chandin','India', 8500, 1,2)  
  8. Insert into tbl_Employee values (8, 'pintu','India', 9500, NULL,1)  
  9. Insert into tbl_Employee values (9, 'Subrat','India', 9800, NULL,1)  
Execute the above query, you should see a message, “Command(s) completed successfully.”
 
Now retrieve all data from “tbl_Employee” table.
  1. select * from tbl_Employee  
output
 
Joining Three Or More Tables In SQL Server 2017
 
General Formula for Joins
  1. SELECT      ColumnList (whatever column you want to display)   
  2. FROM           LeftTableName   
  3. JOIN_TYPE  RightTableName   
  4. ON                 JoinCondition  

 INNER JOIN

 
Inner join returns only the matching rows between both the tables, non matching rows are eliminated.
 
Example
 
Write a query, to retrieve Name, country, Salary, DeptName and Gender from tbl_Employee, tbl_Department table and tbl_Gender.
 
INNER JOIN Query
  1. SELECT Name,country, Salary, DeptName, Gender  
  2. FROM tbl_Employee  
  3. INNER JOIN tbl_Department ON tbl_Employee.DepartmentId = tbl_Department.DeptId  
  4. INNER JOIN tbl_Gender ON tbl_Employee.GenderID = tbl_Gender.GenderId  
OR
  1. SELECT Name,country, Salary, DeptName, Gender  
  2. FROM tbl_Employee  
  3.  JOIN tbl_Department ON tbl_Employee.DepartmentId = tbl_Department.DeptId  
  4.  JOIN tbl_Gender ON tbl_Employee.GenderID = tbl_Gender.GenderId  
Note
JOIN or INNER JOIN means both are the same. It's always better to use INNER JOIN.
 
OR
  1. select emp.Name,emp.country,emp.Salary,dept.DeptName,ge.Gender   
  2.     from tbl_Employee emp   
  3.     inner join tbl_Department dept on emp.DepartmentId=dept.DeptId  
  4.     inner join tbl_Gender ge on emp.GenderID=ge.GenderId  
OutPut
 
Joining Three Or More Tables In SQL Server 2017
 

LEFT JOIN or LEFT OUTER JOIN

 
Left Join or Left Outer Join returns only the matching rows between both the tables, plus non matching rows from the left table.
 
Example
 
Write a query, to retrieve Name, country, Salary, DeptName and Gender from tbl_Employee, tbl_Department table and tbl_Gender.
 
LEFT JOIN or LEFT OUTER JOINQuery
  1. SELECT Name,country, Salary, DeptName, Gender  
  2. FROM tbl_Employee  
  3.  LEFT OUTER JOIN tbl_Department ON tbl_Employee.DepartmentId = tbl_Department.DeptId  
  4.  LEFT OUTER JOIN tbl_Gender ON tbl_Employee.GenderID = tbl_Gender.GenderId  
OR
  1. SELECT Name,country, Salary, DeptName, Gender  
  2. FROM tbl_Employee  
  3.  LEFT JOIN tbl_Department ON tbl_Employee.DepartmentId = tbl_Department.DeptId  
  4.  LEFT JOIN tbl_Gender ON tbl_Employee.GenderID = tbl_Gender.GenderId  
Note
You can use, LEFT JOIN or LEFT OUTER JOIN. OUTER keyword is optional.
 
OR
  1. select emp.Name,emp.country,emp.Salary,dept.DeptName,ge.Gender   
  2.     from tbl_Employee emp   
  3.     Left join tbl_Department dept on emp.DepartmentId=dept.DeptId  
  4.     Left join tbl_Gender ge on emp.GenderID=ge.GenderId  
OutPut
 
Joining Three Or More Tables In SQL Server 2017
 

RIGHT JOIN or RIGHT OUTER JOIN

 
Right Join or Right Outer Join returns only the matching rows between both the tables, plus Non matching rows from the right table.
 
Example
 
Write a query, to retrieve Name, country, Salary, DeptName and Gender from tbl_Employee, tbl_Department table and tbl_Gender.
 
RIGHT JOIN or RIGHT OUTER JOIN Query
  1. SELECT Name,country, Salary, DeptName, Gender  
  2. FROM tbl_Employee  
  3.  RIGHT OUTER JOIN tbl_Department ON tbl_Employee.DepartmentId = tbl_Department.DeptId  
  4.  RIGHT OUTER JOIN tbl_Gender ON tbl_Employee.GenderID = tbl_Gender.GenderId  
OR
  1. SELECT Name,country, Salary, DeptName, Gender  
  2. FROM tbl_Employee  
  3.  RIGHT JOIN tbl_Department ON tbl_Employee.DepartmentId = tbl_Department.DeptId  
  4.  RIGHT JOIN tbl_Gender ON tbl_Employee.GenderID = tbl_Gender.GenderId  
Note
You can use, RIGHT JOIN or RIGHT OUTER JOIN. OUTER keyword is optional.

OR
  1. select emp.Name,emp.country,emp.Salary,dept.DeptName,ge.Gender   
  2.     from tbl_Employee emp   
  3.     Right join tbl_Department dept on emp.DepartmentId=dept.DeptId  
  4.     Right join tbl_Gender ge on emp.GenderID=ge.GenderId  
OutPut
 
Joining Three Or More Tables In SQL Server 2017
 

FULL JOIN or FULL OUTER JOIN

 
Full Join or Full Outer Join returns all rows from both the tables (left & right tables), including non matching rows from both the tables.
 
Example
 
Write a query, to retrieve Name, country, Salary, DeptName and Gender from tbl_Employee, tbl_Department table and tbl_Gender.
 
FULL JOIN or FULL OUTER JOIN Query 
  1. SELECT Name,country, Salary, DeptName, Gender  
  2. FROM tbl_Employee  
  3.  FULL OUTER JOIN tbl_Department ON tbl_Employee.DepartmentId = tbl_Department.DeptId  
  4.  FULL OUTER JOIN tbl_Gender ON tbl_Employee.GenderID = tbl_Gender.GenderId  
OR
  1. SELECT Name,country, Salary, DeptName, Gender  
  2. FROM tbl_Employee  
  3.  FULL JOIN tbl_Department ON tbl_Employee.DepartmentId = tbl_Department.DeptId  
  4.  FULL JOIN tbl_Gender ON tbl_Employee.GenderID = tbl_Gender.GenderId  
Note
You can use, FULL JOIN or FULL OUTER JOIN. OUTER keyword is optional.
 
OR
  1. select emp.Name,emp.country,emp.Salary,dept.DeptName,ge.Gender   
  2.     from tbl_Employee emp   
  3.     Full join tbl_Department dept on emp.DepartmentId=dept.DeptId  
  4.     Full join tbl_Gender ge on emp.GenderID=ge.GenderId  
OutPut
 
Joining Three Or More Tables In SQL Server 2017
 

CROSS JOIN


CROSS JOIN, produces the Cartesian product of the 3 tables.
 
For example, in the tbl_Employee table we have 9 rows, tbl_Department table we have 4 rows and tbl_Gender we have 2 rows. So, a cross join between these 3 tables produces 72 rows. Cross Join shouldn't have ON clause. 
 
Example
 
Write a query, to retrieve Name, country, Salary, DeptName and Gender from tbl_Employee, tbl_Department table and tbl_Gender.

CROSS JOIN Query
  1. SELECT Name,country, Salary, DeptName, Gender  
  2. FROM tbl_Employee  
  3.  CROSS JOIN tbl_Department   
  4.  CROSS JOIN tbl_Gender  
OR
  1. select emp.Name,emp.country,emp.Salary,dept.DeptName,ge.Gender   
  2.     from tbl_Employee emp   
  3.     Cross join tbl_Department dept   
  4.     Cross join tbl_Gender ge   
OutPut
 
Joining Three Or More Tables In SQL Server 2017
 

Conclusion

 
In this article, I explained how to join three or more tables in SQL Server with examples. I hope this article has helped you to understand this topic. Post your valuable feedback in the comments section.