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.
- 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 Database and Two Tables
Step 1 - Create a Database
Open your SQL Server and use the following script to create the “chittadb” Database.
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.