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.

table

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:

  1. SELECT * FROM tblEmployees  
We get the output as:

table design

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.
  1. SELECT * FROM tblDepartments  
EmployeeDepartmentId

For that we can create a query using joins.

Inner Join or Join

It returns only the matching rows from both tables.
  1. SELECT EmployeeId,Name,Gender,City,DepartmentName   
  2. FROM tblEmployees  
  3. JOIN tblDepartments  
  4. 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.

join one table

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.
  1. SELECT EmployeeId,Name,Gender,City,DepartmentName   
  2. FROM tblEmployees  
  3. LEFT JOIN tblDepartments  
  4. ON tblEmployees.EmployeeDepartmentId = tblDepartments.DepartmentId  
OUTER JOIN

And if you want the matching and non-matching rows from the right table, then use a right join or a right outer join.
  1. SELECT EmployeeId,Name,Gender,City,DepartmentName   
  2. FROM tblEmployees  
  3. RIGHT OUTER JOIN tblDepartments  
  4. 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.
  1. SELECT EmployeeId,Name,Gender,City,DepartmentName   
  2. FROM tblEmployees  
  3. FULL OUTER JOIN tblDepartments  
  4. ON tblEmployees.EmployeeDepartmentId = tblDepartments.DepartmentId  
full join

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.
  1. SELECT EmployeeId,Name,Gender,City,DepartmentName   
  2. FROM tblEmployees  
  3. CROSS JOIN tblDepartments  
Cross join

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.
  1. SELECT EmployeeId,Name,Gender,City,DepartmentName   
  2. FROM tblEmployees  
  3. LEFT OUTER JOIN tblDepartments  
  4. ON tblEmployees.EmployeeDepartmentId = tblDepartments.DepartmentId  
  5. WHERE tblEmployees.EmployeeDepartmentId is null;  
matching rows
  1. SELECT EmployeeId,Name,Gender,City,DepartmentName   
  2. FROM tblEmployees  
  3. RIGHT OUTER JOIN tblDepartments  
  4. ON tblEmployees.EmployeeDepartmentId = tblDepartments.DepartmentId  
  5. WHERE tblEmployees.EmployeeDepartmentId is null;  
tblEmployees

If you want the non-matching rows from both tables, then use FULL JOIN.
  1. --Only non-matching rows using FULL JOIN  
  2. SELECT EmployeeId,Name,Gender,City,DepartmentName   
  3. FROM tblEmployees  
  4. FULL OUTER JOIN tblDepartments  
  5. ON tblEmployees.EmployeeDepartmentId = tblDepartments.DepartmentId  
  6. WHERE tblEmployees.EmployeeDepartmentId is null  
  7. OR tblDepartments.DepartmentId is null;  
DepartmentId

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.

tblEmployeeManager

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.
  1. SELECT Employee.Name AS [Employee Name], Manager.Name AS [Manager NameFROM tblEmployeeManager AS Employee  
  2. INNER JOIN tblEmployeeManager AS Manager  
  3. 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.

AS keyword

Only the matching rows are displayed.

Left outer join
  1. SELECT Employee.Name AS [Employee Name], Manager.Name AS [Manager NameFROM tblEmployeeManager AS Employee  
  2. LEFT JOIN tblEmployeeManager AS Manager  
  3. ON Employee.[Manager Id] = Manager.[Employee Id]  
Right outer join

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
  1. SELECT Employee.Name AS [Employee Name], Manager.Name AS [Manager NameFROM tblEmployeeManager AS Employee  
  2. RIGHT JOIN tblEmployeeManager AS Manager  
  3. ON Employee.[Manager Id] = Manager.[Employee Id]  
Manager

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
  1. SELECT Employee.Name AS [Employee Name], Manager.Name AS [Manager NameFROM tblEmployeeManager AS Employee  
  2. FULL JOIN tblEmployeeManager AS Manager  
  3. ON Employee.[Manager Id] = Manager.[Employee Id]  
It will give us all the matching and non-matching rows from both tables.

Full outer join

Cross join
  1. SELECT Employee.Name AS [Employee Name], Manager.Name AS [Manager NameFROM tblEmployeeManager AS Employee  
  2. CROSS JOIN tblEmployeeManager AS Manager  
Cross join image

Retrieving only the non-matching rows from both tables:
  1. SELECT Employee.Name AS [Employee Name], Manager.Name AS [Manager NameFROM tblEmployeeManager AS Employee  
  2. FULL JOIN tblEmployeeManager AS Manager  
  3. ON Employee.[Manager Id] = Manager.[Employee Id]  
  4. WHERE Employee.[Manager Id] IS NULL  
  5. OR Manager.[Employee Id] IS NULL  
*oth the tables