This SQL tutorial focuses on the SQL Server Outer Join statement, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.
This tutorial is a part of several posts describing how to use the JOIN statement in SQL Server. To read additional posts regarding this subject, please use the following links:
- SQL Server Inner JOIN – Joining data items from tables, based on values common to both tables.
- Outer JOIN– Joining data items from tables, based on values common to both tables, while displaying all data from one table regardless of if there is a match on the second table.
- SQL Server Self JOIN– Join a table to itself.
OUTER JOIN
While the SQL Server Inner JOIN statement allows us to retrieve rows from both tables only if they match the join condition, SQL Server Outer JOIN statement retrieves all rows from at least one of the tables, regardless of whether there is a match on the second table.
The illustration above shows the query result of using the SQL Server Inner JOIN in order to retrieve information from Employees and Departments tables.
Note that Andrew does not appear, and neither does the Sales department. The reason for this is the way those tables were compared. The comparison between these two tables was performed by using the following condition:
ON dep.department_id = emp.department_id
That means that as long as the values of the column Department ID in the Employees table are equal to the values of the column Department ID in the Departments table, the row will appear in the query result. If a row does not satisfy a join condition, the row will not appear in the query result.
- Tim does not appear because his department’s ID is NULL, and NULL cannot be compared to any value.
- The IT department does not appear because its ID is 30, and this value cannot be compared to any value in the column Department ID within the Employees table.
To display all of the data items that are found on one table (either left or right), including values that have no comparable data on the second table, use either of the following statements: SQL Server Right OUTER JOIN or SQL Server Left OUTER JOIN.
Left OUTER JOIN
If the Employees table, as described in the illustration above, appears on the left side of the SQL Server JOIN statement.
FROM employees emp JOIN departments emp
(left) (right)
To display all of the employees who exist on the table, including those who do not belong to any department, use the SQL Server Left OUTER JOIN statement :
SELECT emp.last_name , emp.salary , dep.department_name FROM employees emp LEFT OUTER JOIN departments dep ON dep.department_id = emp.department_id
Joining the tables by using Left Outer Join results in displaying all of the employees, including the employees who do not belong to any department (5 rows in total):
Right OUTER JOIN
If the departments table, as described in the illustration above, appears on the right side of the SQL Server JOIN statement.
FROM employees emp JOIN departments dep
(left) (right)
To display all of the departments that exist on the table, including those that do not have any employees, use the SQL Server Right OUTER JOIN statement.
SELECT emp.last_name , emp.salary , dep.department_name FROM employees emp RIGHT OUTER JOIN departments dep ON dep.department_id = emp.department_id
Joining the tables by using the Right Outer JOIN results in displaying all of the departments, including the departments that do not have any employees (5 rows in total):
Full OUTER JOIN
To display the entire data from both tables (including all employees who do not belong to any departments, as well as all departments that do not have any employees), use the SQL Server Full Outer JOIN statement (6 rows in total).
SELECT emp.last_name , emp.salary , dep.department_name FROM employees emp FULL OUTER JOIN departments dep ON dep.department_id = emp.department_id
