Advanced Joins in SQL

Advanced join will provide the non matching row (null) value from the SQL join.

Inner , Left and Right Join Will be added on where table is null to the output of Advanced Joins.

Full Join will added on Where table1.table2 is null or table2.table1 is null to the output of Advanced Joins.

In this session we will learn about

1. Advanced or intelligent joins in SQL Server
2. Retrieve only the non matching rows from the left table
3. Retrieve only the non matching rows from the right table
4. Retrieve only the non matching rows from both the left and right table

Considers Employees (tblEmployee) and Depart

Employee Table (tblEmployee)

Departments Table (tblDepartment)

How to retrieve only the non matching rows from the left table. The output should be as shown below:

Query:
SELECT       Name, Gender, Salary, DepartmentName
FROM           tblEmployee E
LEFT JOIN   tblDepartment D
ON                 E.DepartmentId = D.Id
WHERE        D.Id IS NULL ——-(Advanced Join)—–

How to retrieve only the non matching rows from the right table

Query:
SELECT         Name, Gender, Salary, DepartmentName
FROM             tblEmployee E
RIGHT JOIN    tblDepartment D
ON                   E.DepartmentId = D.Id
WHERE          E.DepartmentId IS NULL ——-(Advanced Join)—–

How to retrieve only the non matching rows from both the left and right table. Matching rows should be eliminated.

Query:
SELECT         Name, Gender, Salary, DepartmentName
FROM              tblEmployee E
FULL JOIN      tblDepartment D
ON                   E.DepartmentId = D.Id
WHERE          E.DepartmentId IS NULL ——-(Advanced Join)—–
OR                   D.Id IS NULL

For More : https://www.youtube.com/watch?v=WnAYlaSTm5E&list=PLX2qHGPZlD6xm99MbNlyHKXy3lPG0HVTq&index=23