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,
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.
  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 has been 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 not null identity(1,1),  
  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 ( 'IT''Chitta''Chennai')  
  2. Insert into tbl_Department values ( 'Payroll''Akhil''Odisha')  
  3. Insert into tbl_Department values ( 'HR''Ram''Pune')  
  4. 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.
  1. select * from tbl_Department  
output
 
Advanced Joins In SQL Server 2017
Step 3 - Create second 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 not null identity(1,1),  
  4.      Name nvarchar(50),  
  5.      Gender nvarchar(50),  
  6.      country nvarchar(20),  
  7.      Salary int,  
  8.      DepartmentId int foreign key references tbl_Department(DeptId)  
  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 ( 'Jitu''Male','India',4000, 1)  
  2. Insert into tbl_Employee values ( 'Rani''Female','India', 5000, 3)  
  3. Insert into tbl_Employee values ( 'Rohit''Male','India', 5500, 1)  
  4. Insert into tbl_Employee values ( 'Dibas''Male','India', 6500, 2)  
  5. Insert into tbl_Employee values ( 'Gajendra''Male','India', 3800, 2)  
  6. Insert into tbl_Employee values ( 'Raja''Male','India', 9000, 1)  
  7. Insert into tbl_Employee values ( 'Jeni''Female','India', 5800, 3)  
  8. Insert into tbl_Employee values ( 'Chandin''Female','India', 8500, 1)  
  9. Insert into tbl_Employee values ( 'pintu''Male','India', 9500, NULL)  
  10. 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.
  1. select * from tbl_Employee  
output
 
Advanced Joins 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  
Retrieve only the non matching rows from the left table
JOIN Query
  1. SELECT Name, Gender,country, Salary, DeptName   
  2. FROM tbl_Employee  
  3. LEFT OUTER JOIN tbl_Department  
  4. ON tbl_Employee.DepartmentId = tbl_Department.DeptId  
  5. where tbl_Employee.DepartmentId is null  
OR
  1. SELECT Name, Gender,country, Salary, DeptName   
  2. FROM tbl_Employee  
  3. LEFT OUTER JOIN tbl_Department  
  4. ON tbl_Employee.DepartmentId = tbl_Department.DeptId  
  5. where tbl_Department.DeptId is null  
OR
  1. SELECT Name, Gender,country, Salary, DeptName   
  2. FROM           tbl_Employee E  
  3. LEFT JOIN   tbl_Department D  
  4. ON                 E.DepartmentId = D.DeptId  
  5. WHERE        D.DeptId IS NULL  
OR
  1. SELECT Name, Gender,country, Salary, DeptName   
  2. FROM           tbl_Employee E  
  3. LEFT JOIN   tbl_Department D  
  4. ON                 E.DepartmentId = D.DeptId  
  5. WHERE        E.DepartmentId IS NULL  
Note
To achieve this we will have to use the WHERE clause in our query.
 
OutPut
 
Advanced Joins In SQL Server 2017
 
How to retrieve only the non matching rows from the right table
 
JOIN Query
  1. SELECT Name, Gender,country, Salary, DeptName   
  2. FROM tbl_Employee  
  3. RIGHT JOIN tbl_Department  
  4. ON tbl_Employee.DepartmentId = tbl_Department.DeptId  
  5. where tbl_Employee.DepartmentId is null  
OR
  1. SELECT Name, Gender,country, Salary, DeptName   
  2. FROM           tbl_Employee E  
  3. RIGHT JOIN   tbl_Department D  
  4. ON             E.DepartmentId = D.DeptId  
  5. where E.DepartmentId is null  
Note
To achieve this we will have to use the WHERE clause in our query.
 
OutPut
 
Advanced Joins In SQL Server 2017
Retrieve only the non matching rows from both the left and right table
 
JOIN Query
  1. SELECT Name, Gender,country, Salary, DeptName   
  2. FROM tbl_Employee  
  3. FULL JOIN tbl_Department  
  4. ON tbl_Employee.DepartmentId = tbl_Department.DeptId  
  5. where tbl_Employee.DepartmentId is null   
  6. OR tbl_Department.DeptId is null  
OR
  1. SELECT Name, Gender,country, Salary, DeptName   
  2. FROM           tbl_Employee E  
  3. FULL JOIN   tbl_Department D  
  4. ON             E.DepartmentId = D.DeptId  
  5. where E.DepartmentId is null   
  6. OR D.DeptId is null  
Note
To achieve this we will have to use the WHERE and OR clause in our query.
 
OutPut
 
Advanced Joins In SQL Server 2017
 

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.