Advanced Joins In SQL Server 2017

Introduction

In this article, I am going to explain advanced joins in SQL Server with examples. 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 articles using the below links before reading this article,

Definition

It is used to fetch/retrieve data from two or more related tables from a database. In general, tables are related to each other using foreign key constraints.

Types of Joins

Here we have to use some advanced join queries below.

Retrieve only the non-matching rows from the left table
Retrieve only the non-matching rows from the right table
Retrieve only the non-matching rows from both the left and right table

Prerequisites

SQL Server 2017 or you can use SQL Server 2008 or above version.

Now, first, we will create a Database and two tables to apply the joins for understanding.

Creating a Database and Two 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 has been 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 not null identity(1,1),
    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 ('IT', 'Chitta', 'Chennai')
Insert into tbl_Department values ('Payroll', 'Akhil', 'Odisha')
Insert into tbl_Department values ('HR', 'Ram', 'Pune')
Insert into tbl_Department values ('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

Department table

Step 3. Create a second table.

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

Create table tbl_Employee
(
    EmpID int primary key not null identity(1,1),
    Name nvarchar(50),
    Gender nvarchar(50),
    country nvarchar(20),
    Salary int,
    DepartmentId int foreign key references tbl_Department(DeptId)
)

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 ('Jitu', 'Male', 'India', 4000, 1)
Insert into tbl_Employee values ('Rani', 'Female', 'India', 5000, 3)
Insert into tbl_Employee values ('Rohit', 'Male', 'India', 5500, 1)
Insert into tbl_Employee values ('Dibas', 'Male', 'India', 6500, 2)
Insert into tbl_Employee values ('Gajendra', 'Male', 'India', 3800, 2)
Insert into tbl_Employee values ('Raja', 'Male', 'India', 9000, 1)
Insert into tbl_Employee values ('Jeni', 'Female', 'India', 5800, 3)
Insert into tbl_Employee values ('Chandin', 'Female', 'India', 8500, 1)
Insert into tbl_Employee values ('pintu', 'Male', 'India', 9500, NULL)
Insert into tbl_Employee values ('Subrat', 'Male', 'India', 9800, NULL)

Execute the above query, and you should see a message, “Command(s) completed successfully.”

Now retrieve all data from the “tbl_Employee” table.

SELECT * FROM tbl_Employee

Output

Employee table

General Formula for Joins

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

Retrieve only the non-matching rows from the left table

JOIN Query

SELECT Name, Gender, country, Salary, DeptName
FROM tbl_Employee
LEFT OUTER JOIN tbl_Department
ON tbl_Employee.DepartmentId = tbl_Department.DeptId
WHERE tbl_Employee.DepartmentId IS NULL

SELECT Name, Gender, country, Salary, DeptName
FROM tbl_Employee
LEFT OUTER JOIN tbl_Department
ON tbl_Employee.DepartmentId = tbl_Department.DeptId
WHERE tbl_Department.DeptId IS NULL

SELECT Name, Gender, country, Salary, DeptName
FROM tbl_Employee E
LEFT JOIN tbl_Department D
ON E.DepartmentId = D.DeptId
WHERE D.DeptId IS NULL

SELECT Name, Gender, country, Salary, DeptName
FROM tbl_Employee E
LEFT JOIN tbl_Department D ON E.DepartmentId = D.DeptId
WHERE E.DepartmentId IS NULL

Note. To achieve this we will have to use the WHERE clause in our query.

Output

Query

How do we retrieve only the non-matching rows from the right table?

JOIN Query

SELECT Name, Gender, country, Salary, DeptName
FROM tbl_Employee
RIGHT JOIN tbl_Department
ON tbl_Employee.DepartmentId = tbl_Department.DeptId
WHERE tbl_Employee.DepartmentId IS NULL

SELECT Name, Gender, country, Salary, DeptName
FROM tbl_Employee E
RIGHT JOIN tbl_Department D ON E.DepartmentId = D.DeptId
WHERE E.DepartmentId IS NULL

Note. To achieve this we will have to use the WHERE clause in our query.

Output

Null

Retrieve only the non-matching rows from both the left and right table

JOIN Query

SELECT Name, Gender, country, Salary, DeptName
FROM tbl_Employee
FULL JOIN tbl_Department
ON tbl_Employee.DepartmentId = tbl_Department.DeptId
WHERE tbl_Employee.DepartmentId IS NULL
OR tbl_Department.DeptId IS NULL

SELECT Name, Gender, country, Salary, DeptName
FROM tbl_Employee E
FULL JOIN tbl_Department D
ON E.DepartmentId = D.DeptId
WHERE E.DepartmentId IS NULL
OR D.DeptId IS NULL

Note. To achieve this we will have to use the WHERE and OR clause in our query.

Output

Output

Conclusion

In this article, I explained Advanced joins in SQL Server with examples. I hope this article has helped you to understand this topic. Post your valuable feedback in the comments section.