SQL Outer Join

Outer Joins allow retrieval of rows from two or more tables based on a related column. Unlike Inner Joins, Outer Joins also include rows that do not have a corresponding match in one or both of the tables. This makes them especially useful when dealing with incomplete data, ensuring that no records are missed during reporting or analysis.

Types of Outer Joins

There are three main types of Outer Joins in SQL:

• LEFT OUTER JOIN (or LEFT JOIN)
• RIGHT OUTER JOIN (or RIGHT JOIN)
• FULL OUTER JOIN

Each of these join types handles unmatched rows differently and understanding how they work will help you use them effectively in your SQL queries.

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

1. Employees Table

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50),
    DepartmentID INT );

INSERT INTO Employees (EmployeeID, Name, DepartmentID) VALUES
(1, 'John', 101),
(2, 'Sarah', 102),
(3, 'Michael', NULL),
(4, 'Emma', 103);

2. Departments Table

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50));

INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
(101, 'HR'),
(102, 'IT'),
(103, 'Marketing')
(104, 'Finance');

Now we’ll use these tables in our join queries.

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.

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

LEFT JOIN ensures Michael still appears in the result even though he has no department. The DepartmentName is shown as NULL to indicate no match.

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.

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

The Finance department (104) has no employees, so the employee fields appear as NULL.

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.

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

Michael has no department (so DepartmentName is NULL) and Finance has no employees (so Name and DepartmentID are NULL).

When to Use SQL Outer Joins?

Outer Joins are particularly useful when:

• One need to include all records from one or both tables, even if no match exists.
• Handling incomplete or missing relationships, ensuring no important records are lost.
• Creating comprehensive reports that must show all employees, departments or entities.
• Merging datasets from different sources where some records may not align perfectly.

Difference Between INNER JOIN and OUTER JOIN