What is Outer Join: Definition, Examples, and Use Cases

An outer join in a database combines rows from two tables and includes all rows from one or both tables, even if there is no matching row in the other table. It returns matched rows plus unmatched rows with NULL in place of missing values. Outer joins come in three types: LEFT, RIGHT, and FULL.

How It Works

Imagine you have two lists of friends: one list of friends who like pizza and another list of friends who like ice cream. A regular join would only show friends who like both pizza and ice cream. But an outer join lets you see all friends from one or both lists, even if they only like one of the two treats.

In database terms, an outer join returns all rows from one or both tables. If a row in one table has no matching row in the other, the result still includes that row, but fills in NULL for missing data. This helps you find unmatched or missing connections between tables.

Example

This example shows a LEFT OUTER JOIN between two tables: Employees and Departments. It lists all employees and their departments, including employees without a department.

sql

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

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

INSERT INTO Employees VALUES (1, 'Alice', 10), (2, 'Bob', NULL), (3, 'Charlie', 20);
INSERT INTO Departments VALUES (10, 'HR'), (20, 'IT');

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

Output

Name    | DepartmentName
--------|---------------
Alice   | HR
Bob     | NULL
Charlie | IT

When to Use

Use an outer join when you want to keep all records from one or both tables, even if some do not have matching data. This is useful for finding missing links or showing complete lists with optional related data.

For example, you might want to list all customers and their orders, including customers who have not placed any orders yet. A LEFT OUTER JOIN helps show those customers with NULL for order details.

Key Points

• LEFT OUTER JOIN: Includes all rows from the left table and matched rows from the right.
• RIGHT OUTER JOIN: Includes all rows from the right table and matched rows from the left.
• FULL OUTER JOIN: Includes all rows from both tables, matched or not.
• Unmatched rows show NULL for missing columns.
• Outer joins help find unmatched or missing relationships between tables.

Key Takeaways

An outer join returns all rows from one or both tables, including unmatched rows with NULLs.

LEFT, RIGHT, and FULL are the three types of outer joins, differing by which table's rows are fully included.

Use outer joins to find missing matches or to show complete data sets with optional related information.

Unmatched rows in the result have NULL values for columns from the other table.

Outer joins are essential for comprehensive data analysis involving multiple tables.