Advanced Joins In SQL Server 2017

Introduction

 

In this article, I am going to explain about 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,

• Joins In SQL Server 2017
• Self Join In SQL Server 2017

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 query in below.

1. Retrieve only the non matching rows from the left table
2. Retrieve only the non matching rows from the right table
3. 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 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 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 “tbl_Department” table.

select * from tbl_Department

output

 

Step 3 - Create 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 “tbl_Employee” table.

select * from tbl_Employee

output

 

 

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

OR

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

OR

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

OR

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

 

 

How to 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

OR

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

 

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

OR

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

 

 

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.