SQL Outer Join (original) (raw)

Last Updated : 14 Apr, 2026

SQL provides the OUTER JOIN to return both matching and non-matching rows from two tables. It includes unmatched records by filling missing values with NULL.

• It returns matching rows from both tables.
• It also includes non-matching rows with NULL values.
• It is more inclusive than INNER JOIN.

Let's create the two tables, Employees and Departments for understanding all types of Outer Joins with examples.

Employees Table

Departments Table

Types of Outer Joins

There are three main types of Outer Joins in SQL:

1. LEFT OUTER JOIN (or LEFT JOIN)

LEFT OUTER JOIN (referred to as LEFT JOIN) returns all rows from the left table and the matching rows from the right table. If there is no match, the result will include NULL values for columns from the right table.

LEFT OUTER JOIN (or LEFT JOIN)

**Syntax:

SELECT table1.column1, table1.column2, table2.column1, ... FROM table1 LEFT JOIN table2 ON table1.matching_column = table2.matching_column;

**Example: To retrieve all employees along with their respective departments, even if they don't belong to any department (i.e., the department is NULL), we can use the LEFT OUTER JOIN

**Query:

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

**Output

2. RIGHT OUTER JOIN (RIGHT JOIN)

RIGHT OUTER JOIN (often called RIGHT JOIN) returns all rows from the right table and the matching rows from the left table. If there is no match, the result will include NULL values for columns from the left table.

RIGHT OUTER JOIN (RIGHT JOIN)

**Syntax:

SELECT table1.column1, table1.column2, table2.column1, ... FROM table1 RIGHT JOIN table2 ON table1.matching_column = table2.matching_column;

**Example: Let’s now look at a RIGHT OUTER JOIN on the Employees and Departments tables. Suppose we want to retrieve all departments, even if no employees belong to a specific department.

**Query:

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

**Output

3. FULL OUTER JOIN

FULL OUTER JOIN returns all rows when there is a match in either the left or right table. If there is no match, the result will include NULL for the missing side of the table. Essentially, it combines the results of both LEFT JOIN and RIGHT JOIN.

FULL OUTER JOIN

**Syntax:

SELECT table1.column1, table1.column2, table2.column1, ... FROM table1 FULL JOIN table2 ON table1.matching_column = table2.matching_column;

**Example: Let’s now use a FULL OUTER JOIN to get all employees and all departments, regardless of whether an employee belongs to a department or a department has employees.

**Query:

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

**Output

INNER JOIN Vs OUTER JOIN

Here are the detailed comparison between INNER JOIN and OUTER JOIN: