JOIN in SQL Server - SQL Server Guides

In this SQL Server tutorial, I will explain how to use JOIN in SQL Server to merge the rows from multiple tables.

In our database, information is stored in different tables, as all the information doesn’t exist in one place, so sometimes we need to make informed decisions based on the information in other tables.

JOIN is a way to fetch records from multiple tables; in this tutorial, you will learn how to use JOIN with practical examples and types of JOIN and their syntax.

Table of Contents

What is JOIN in SQL Server

SQL Server Join allows you to get the records from multiple (more than one) tables. If you have two tables, both are related, such as employees and departments. You can use the JOIN to combine rows from both tables and retrieve the required information.

For example, you can find the employee department name and code based on the employee ID.

As you know, data are separated into different tables, which means we don’t permanently store them all in the same tables; for better organization and understanding of information, data are kept in separate tables.

Usually, we need to query the table for information based on some information in another table, so we require a way to combine information from the different tables; here, using the concepts of JOIN, you can join or unite the rows or data from the other tables into a single result set.

The general syntax is below, which you can use to join two or more tables in SQL Server.

SELECT  t1.column_name, t2.column_name
FROM table_name1 as t1
JOIN table_name2 as t2 ON join_condtion

Where,

t1.column_name, t2.column_name: These are columns you want to include in the result set, and columns can be from tables you combine; for example, here, t1 and t2 are the first and second tables from which you are accessing the column_name.
JOIN table_name2 ON join_condition: To join the second table with the first table_name1, specify the JOIN keyword followed by table_name2, and specify on what condition you want to combine the tables
- To specify the condition, use the ON keyword followed by join_condition; here, in the condition, you need to specify the common column in both tables and also ensure common columns must have the same data type.
- Also, remember in the condition, tables are joined based on the foreign key in one table and its associated primary key in another table. Both common columns are separated using the operator (=, or <, >) in this condition.

Using the above syntax, you can combine more than one table.

SQL Server JOIN Types

But JOIN in SQL Server has different types of join, such as:

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN

The above joins differ in how they retrieve the information from multiple tables. Let’s move and learn about each join one by one.

INNER JOIN in SQL Server

SQL Server INNER JOIN returns the only matching rows in both tables. The syntax is given below.

SELECT  t1.column_name, t2.column_name
FROM table_name1 as t1
INNER JOIN table_name2 as t2 ON join_condtion

To combine more than two tables to retrieve only matching rows, use the INNER JOIN keyword, as shown in the syntax.

For example, you have two tables, Departments and Employee, as shown below.

As you can see, the Department table contains the DepartmentID and DepartmentName columns. The Employees table contains three columns EmployeeID, Name and DeptID.

Look at the DepartmentID of Department table and DeptID of Employees columns, which contain the same values and the column data type. Also, the DeptID column contains a NULL value, which means the department doesn’t exist for some employees.

So, in the condition of JOIN, we will use these two columns to combine both tables. So here you have a task, where you need to find all the employees in a department. For that, use INNER JOIN, as shown below, using the query.

SELECT  Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DeptID = Departments.DepartmentID;

After executing the above query, the result set contains employee names who belong to the specific department. For example, Lane belongs to Human Resources, Eva belongs to Sales, etc.

But as you know, some of the employees without DeptID are not included in the result; that is because of the INNER JOIN as it only returns the matching rows, so it returns all the employees who have department ID, but it doesn’t include the employee who is without department ID.

Because the department ID values in the DepartmentID column of the Employees table don’t match the department ID values in the DeptID column of the Departments table. So that the result only contains the employees from matching department IDs.

This is how to use INNER JOIN in SQL Server.

LEFT JOIN in SQL Server

The LEFT JOIN in SQL Server returns all the rows from the left table and matching rows from the right table.

The syntax is given below.

SELECT  t1.column_name, t2.column_name
FROM table_name1 as t1
LEFT JOIN table_name2 as t2 ON join_condtion

In your query, if you want to include all the rows from the left table and only match rows from the right based on the related column, then use the LEFT JOIN.

For example, when you have performed the INNER JOIN, it returns the employees who belong to the department but doesn’t return the employees who don’t belong to any department.

Let’s say you also want to find all employees and their department names, including those without a department. For that, use the LEFT JOIN as shown using the query below.

SELECT  Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DeptID = Departments.DepartmentID;

From the output, the result set also contains the employees without a department. Because of LEFT JOIN, these employees are included as they exist in the Employees table.

This is how to use the LEFT JOIN in SQL Server to retrieve all the records from the left table and only match records from the right table.

RIGHT JOIN in SQL Server

RIGHT JOIN is the opposite of LEFT JOIN; the RIGHT JOIN in SQL Server returns all the rows from the right table and matching rows from the left table.

The syntax is given below.

SELECT  t1.column_name, t2.column_name
FROM table_name1 as t1
RIGHT JOIN table_name2 as t2 ON join_condtion

For example, you must write a query that must return all the departments, including those without employees.

SELECT  Employees.Name, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments ON Employees.DeptID = Departments.DepartmentID;

As you can see, the result set contains all the departments, including those without employees.

This is how to use the RIGHT JOIN in SQL Server to retrieve all the records from the right table and only match records from the left table.

FULL OUTER JOIN in SQL Server

The FULL OUTER JOIN in SQL Server returns all the records from more than one table with or without matching rows. FULL OUTER JOIN means combining LEFT and RIGHT JOIN in a query.

The syntax is given below.

SELECT  t1.column_name, t2.column_name
FROM table_name1 as t1
FULL OUTER JOIN table_name2 as t2 ON join_condtion

For example, you must find all employees and departments, with or without matches; for that, use the query below.

SELECT  Employees.Name, Departments.DepartmentName
FROM Employees
FULL OUTER JOIN Departments ON Employees.DeptID = Departments.DepartmentID;

In the result set, all the employees and departments are included with or without matches; as you can see, employees with the departments and departments without employees are included.

Conclusion

In this SQL Server tutorial, you have returned the rows from the Employees and Departments table by combining both using JOIN in SQL Server.

You learned about INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN in SQL Server, which helps retrieve rows differently.