Joins in SQL Server

The various types of joins in SQL Server are:

• Cross join
• Inner join
• Outer join: left, right and full outer join
• Self join

For the demo we will be using the following tables.



From foreign key we know the primary key column of one table can be the foreign key of another and here DepartmentId is the primary column of the tblDepartments table and it is a foreign key column “EmployeeDepartmentId” of the tblEmployees table.

Before writing any query let's first understand the purpose of joins.

To retrieve data from two or more related tables, joins can be used.

After reading the purpose you must be wondering how two or more tables can be related to each.

Using foreign key constraints.

DEMO

When we execute the query:

SELECT * FROM tblEmployees

We get the output as:



But our business requirement is that we need to write a query that will display all the columns of the tblEmployees table except the EmployeeDepartmentId column and in that place we want to show the DepartmentName column of the tblDepartments table.

SELECT * FROM tblDepartments

For that we can create a query using joins.

Inner Join or Join

It returns only the matching rows from both tables.

SELECT EmployeeId,Name,Gender,City,DepartmentName
FROM tblEmployees
JOIN tblDepartments
ON tblEmployees.EmployeeDepartmentId = tblDepartments.DepartmentId

To join one table with another, we say Join or Inner Join.

Whenever we use joins in a query, we use an ON clause to give a condition, to filter the records we use a WHERE clause and if the match is found, we get the output.

If we execute the preceding query, we will get only the matching rows.



OUTER JOIN

Now let's say we want all the matching rows and the matching rows from the left table, for that we can use a left join or a left outer join.

SELECT EmployeeId,Name,Gender,City,DepartmentName
FROM tblEmployees
LEFT JOIN tblDepartments
ON tblEmployees.EmployeeDepartmentId = tblDepartments.DepartmentId

And if you want the matching and non-matching rows from the right table, then use a right join or a right outer join.

SELECT EmployeeId,Name,Gender,City,DepartmentName
FROM tblEmployees
RIGHT OUTER JOIN tblDepartments
ON tblEmployees.EmployeeDepartmentId = tblDepartments.DepartmentId

If we want all the matching and non-matching rows from both tables, we can use full join or full outer join.

SELECT EmployeeId,Name,Gender,City,DepartmentName
FROM tblEmployees
FULL OUTER JOIN tblDepartments
ON tblEmployees.EmployeeDepartmentId = tblDepartments.DepartmentId

Cross join

It produces the Cartesian product of the two tables, meaning if there are 15 records in one table and 4 records in another table, it will multiple 15 and 4 and will display 60 records.

A cross join cannot have an ON clause.

It takes the records from the right table and associates them with the left table records.

SELECT EmployeeId,Name,Gender,City,DepartmentName
FROM tblEmployees
CROSS JOIN tblDepartments

Until now we know that a left join returns all the matching and non-matching rows from the table and the matching rows from the right table and a right join does the opposite of that.

Non-matching rows

Let's say we want only the non-matching rows from both tables using a left and right join.

SELECT EmployeeId,Name,Gender,City,DepartmentName
FROM tblEmployees
LEFT OUTER JOIN tblDepartments
ON tblEmployees.EmployeeDepartmentId = tblDepartments.DepartmentId
WHERE tblEmployees.EmployeeDepartmentId is null;

SELECT EmployeeId,Name,Gender,City,DepartmentName
FROM tblEmployees
RIGHT OUTER JOIN tblDepartments
ON tblEmployees.EmployeeDepartmentId = tblDepartments.DepartmentId
WHERE tblEmployees.EmployeeDepartmentId is null;

If you want the non-matching rows from both tables, then use FULL JOIN.

--Only non-matching rows using FULL JOIN
SELECT EmployeeId,Name,Gender,City,DepartmentName
FROM tblEmployees
FULL OUTER JOIN tblDepartments
ON tblEmployees.EmployeeDepartmentId = tblDepartments.DepartmentId
WHERE tblEmployees.EmployeeDepartmentId is null
OR tblDepartments.DepartmentId is null;

Self Join

A Self Join is nothing but a join that joins a table with itself.

It can be classified into an Inner Self join, Outer Self Join (left, right and full) and Cross Self Join.

Now let's look at an example.

I will be using this tblEmployeeManager table for the demo.



In the table above, we have the three columns EmployeeId, Name and ManagerId. If you look at the managerId for employee Sam it is 2 which means James is the manager of Sam, Sara is the manager of James and Aiden is the manager of Sara whereas Aiden's manager id is null which means Aiden has no manager.

Now I want to write a query that will provide the employee name with its manager name and for that we can use a self join.

SELECT Employee.Name AS [Employee Name], Manager.Name AS [Manager Name] FROM tblEmployeeManager AS Employee
INNER JOIN tblEmployeeManager AS Manager
ON Employee.[Manager Id] = Manager.[Employee Id]

From the preceding query we are selecting the name columns, first is the Employee name and the second one is the manager name.

We are then joining the tblEmployeeManager with itself and then in the end we are checking if the ManagerId of the employee is matching with the Employee Id of the manager.

The AS keyword is used here to provide an alias.



Only the matching rows are displayed.

Left outer join

SELECT Employee.Name AS [Employee Name], Manager.Name AS [Manager Name] FROM tblEmployeeManager AS Employee
LEFT JOIN tblEmployeeManager AS Manager
ON Employee.[Manager Id] = Manager.[Employee Id]

In the preceding output, we are getting all the matching and non-matching rows from the left table and all the matching rows from the right table.

Right outer join

SELECT Employee.Name AS [Employee Name], Manager.Name AS [Manager Name] FROM tblEmployeeManager AS Employee
RIGHT JOIN tblEmployeeManager AS Manager
ON Employee.[Manager Id] = Manager.[Employee Id]

In the preceding output, we are getting all the matching data from the left table and all the matching rows and non-matching rows from the right table.

Full outer join

SELECT Employee.Name AS [Employee Name], Manager.Name AS [Manager Name] FROM tblEmployeeManager AS Employee
FULL JOIN tblEmployeeManager AS Manager
ON Employee.[Manager Id] = Manager.[Employee Id]

It will give us all the matching and non-matching rows from both tables.



Cross join

SELECT Employee.Name AS [Employee Name], Manager.Name AS [Manager Name] FROM tblEmployeeManager AS Employee
CROSS JOIN tblEmployeeManager AS Manager

Retrieving only the non-matching rows from both tables:

SELECT Employee.Name AS [Employee Name], Manager.Name AS [Manager Name] FROM tblEmployeeManager AS Employee
FULL JOIN tblEmployeeManager AS Manager
ON Employee.[Manager Id] = Manager.[Employee Id]
WHERE Employee.[Manager Id] IS NULL
OR Manager.[Employee Id] IS NULL

*