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 a Database and three tables

Step 1. Create a Database

Open your SQL Server and use the following script to create the “chittadb” Database.

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 the first table

Open your SQL Server and use the following script to create table “tbl_Department”.

CREATE TABLE tbl_Department (
    DeptId INT PRIMARY KEY,
    DeptName NVARCHAR(50),
    DeptHead NVARCHAR(50),
    Location NVARCHAR(100)
);

Execute the above query to create “tbl_Department “.

You should see a message, “Command(s) completed successfully.”

Now, data is inserted into the table.

INSERT INTO tbl_Department VALUES (1, 'IT', 'Chitta', 'Chennai');
INSERT INTO tbl_Department VALUES (2, 'Payroll', 'Akhil', 'Odisha');
INSERT INTO tbl_Department VALUES (3, 'HR', 'Ram', 'Pune');
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 the “tbl_Department” table.

SELECT * FROM tbl_Department;

Output

DeptId

Step 3. Create the second table

Open your SQL Server and use the following script to create table “tbl_Gender”.

CREATE TABLE tbl_Gender (
    GenderId INT PRIMARY KEY,
    Gender NVARCHAR(50)
);

Execute the above query to create “tbl_Gender “.

You should see a message, “Command(s) completed successfully.”

Now, data is inserted into the table.

INSERT INTO tbl_Gender VALUES (1, 'Male');
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 the “tbl_Gender” table.

SELECT * FROM tbl_Gender;

Output

GenderId

Step 4. Create a third table

Open your SQL Server and use the following script to create table “tbl_Employee”.

CREATE TABLE tbl_Employee
(
    EmpId INT PRIMARY KEY,
    Name NVARCHAR(50),
    Country NVARCHAR(20),
    Salary INT,
    DepartmentId INT FOREIGN KEY REFERENCES tbl_Department(DeptId),
    GenderID INT FOREIGN KEY REFERENCES tbl_Gender(GenderId)
);

Execute the above query to create “tbl_Employee “.

You should see a message, “Command(s) completed successfully.”

Now, data is inserted into the table.

INSERT INTO tbl_Employee VALUES (1, 'Jitu', 'India', 4000, 1, 1);
INSERT INTO tbl_Employee VALUES (2, 'Rani', 'India', 5000, 3, 2);
INSERT INTO tbl_Employee VALUES (3, 'Dibas', 'India', 6500, 2, 1);
INSERT INTO tbl_Employee VALUES (4, 'Gajendra', 'India', 3800, 2, 1);
INSERT INTO tbl_Employee VALUES (5, 'Raja', 'India', 9000, 1, 1);
INSERT INTO tbl_Employee VALUES (6, 'Jeni', 'India', 5800, 3, 2);
INSERT INTO tbl_Employee VALUES (7, 'Chandin', 'India', 8500, 1, 2);
INSERT INTO tbl_Employee VALUES (8, 'pintu', 'India', 9500, NULL, 1);
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 the “tbl_Employee” table.

SELECT * FROM tbl_Employee;

Output

EmpId

General Formula for Joins.

SELECT ColumnList -- (whatever column you want to display)
FROM LeftTableName
JOIN_TYPE RightTableName
ON JoinCondition

INNER JOIN

Inner join returns only the matching rows between both tables, nonmatching 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

SELECT Name, country, Salary, DeptName, Gender
FROM tbl_Employee
INNER JOIN tbl_Department ON tbl_Employee.DepartmentId = tbl_Department.DeptId
INNER JOIN tbl_Gender ON tbl_Employee.GenderID = tbl_Gender.GenderId;

SELECT Name, country, Salary, DeptName, Gender
FROM tbl_Employee
JOIN tbl_Department ON tbl_Employee.DepartmentId = tbl_Department.DeptId
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.

SELECT emp.Name, emp.country, emp.Salary, dept.DeptName, ge.Gender
FROM tbl_Employee emp
INNER JOIN tbl_Department dept ON emp.DepartmentId = dept.DeptId
INNER JOIN tbl_Gender ge ON emp.GenderID = ge.GenderId;

Output

INNER JOIN Query

LEFT JOIN or LEFT OUTER JOIN

Left Join or Left Outer Join returns only the matching rows between both tables, plus nonmatching 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

SELECT Name, country, Salary, DeptName, Gender
FROM tbl_Employee
LEFT OUTER JOIN tbl_Department ON tbl_Employee.DepartmentId = tbl_Department.DeptId
LEFT OUTER JOIN tbl_Gender ON tbl_Employee.GenderID = tbl_Gender.GenderId;

SELECT Name, country, Salary, DeptName, Gender
FROM tbl_Employee
LEFT JOIN tbl_Department ON tbl_Employee.DepartmentId = tbl_Department.DeptId
LEFT JOIN tbl_Gender ON tbl_Employee.GenderID = tbl_Gender.GenderId;

Note. You can use, LEFT JOIN or LEFT OUTER JOIN. The OUTER keyword is optional.

SELECT emp.Name, emp.country, emp.Salary, dept.DeptName, ge.Gender
FROM tbl_Employee emp
LEFT JOIN tbl_Department dept ON emp.DepartmentId = dept.DeptId
LEFT JOIN tbl_Gender ge ON emp.GenderID = ge.GenderId;

Output

LEFT OUTER JOIN

RIGHT JOIN or RIGHT OUTER JOIN

Right Join or Right Outer Join returns only the matching rows between both 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

SELECT Name, country, Salary, DeptName, Gender
FROM tbl_Employee
RIGHT OUTER JOIN tbl_Department ON tbl_Employee.DepartmentId = tbl_Department.DeptId
RIGHT OUTER JOIN tbl_Gender ON tbl_Employee.GenderID = tbl_Gender.GenderId;

SELECT Name, Country, Salary, DeptName, Gender
FROM tbl_Employee
RIGHT JOIN tbl_Department ON tbl_Employee.DepartmentId = tbl_Department.DeptId
RIGHT JOIN tbl_Gender ON tbl_Employee.GenderID = tbl_Gender.GenderId;

Note. You can use, RIGHT JOIN or RIGHT OUTER JOIN. The OUTER keyword is optional.

SELECT emp.Name, emp.country, emp.Salary, dept.DeptName, ge.Gender
FROM tbl_Employee emp
RIGHT JOIN tbl_Department dept ON emp.DepartmentId = dept.DeptId
RIGHT JOIN tbl_Gender ge ON emp.GenderID = ge.GenderId;

Output

RIGHT JOIN

FULL JOIN or FULL OUTER JOIN

Full Join or Full Outer Join returns all rows from both tables (left & right tables), including non-matching rows from both 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

SELECT Name, Country, Salary, DeptName, Gender
FROM tbl_Employee
FULL OUTER JOIN tbl_Department ON tbl_Employee.DepartmentId = tbl_Department.DeptId
FULL OUTER JOIN tbl_Gender ON tbl_Employee.GenderID = tbl_Gender.GenderId;

SELECT Name, country, Salary, DeptName, Gender
FROM tbl_Employee
FULL JOIN tbl_Department ON tbl_Employee.DepartmentId = tbl_Department.DeptId
FULL JOIN tbl_Gender ON tbl_Employee.GenderID = tbl_Gender.GenderId;

Note. You can use, FULL JOIN or FULL OUTER JOIN. The OUTER keyword is optional.

SELECT emp.Name, emp.country, emp.Salary, dept.DeptName, ge.Gender
FROM tbl_Employee emp
FULL JOIN tbl_Department dept ON emp.DepartmentId = dept.DeptId
FULL JOIN tbl_Gender ge ON emp.GenderID = ge.GenderId;

Output

FULL JOIN

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 in tbl_Gender we have 2 rows. So, a cross join between these 3 tables produces 72 rows. Cross Join shouldn't have an 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

SELECT Name, Country, Salary, DeptName, Gender
FROM tbl_Employee
CROSS JOIN tbl_Department
CROSS JOIN tbl_Gender;

SELECT emp.Name, emp.country, emp.Salary, dept.DeptName, ge.Gender
FROM tbl_Employee emp
CROSS JOIN tbl_Department dept
CROSS JOIN tbl_Gender ge;

Output

CROSS JOIN

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.