Outer Join in SQL - Left, Right and Full

OUTER JOIN statement in SQL is a powerful command that allows you to retrieve data from multiple tables based on a related column, even if there are no matching values.

Unlike INNER JOIN, which only returns matching records, OUTER JOIN includes all rows from one table and the matching rows from another table.

Read more about Inner Join

Types of OUTER JOINs

There are three primary types of OUTER JOINs: LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.

LEFT OUTER JOIN

A LEFT OUTER JOIN returns all records from the left table and the matched records from the right table.

Syntax:

SELECT *
FROM left_table
LEFT OUTER JOIN right_table ON left_table.id = right_table.id;

RIGHT OUTER JOIN

The RIGHT OUTER JOIN is similar to the LEFT OUTER JOIN but returns all records from the right table and the matched records from the left table. If there is no match, NULL values are returned for columns from the left table.

Syntax:

SELECT *
FROM left_table
RIGHT OUTER JOIN right_table ON left_table.id = right_table.id;

FULL OUTER JOIN

The FULL OUTER JOIN returns all records when there is a match in either the left or right table. If there is no match, NULL values are returned for columns from the table without a match.

Syntax:

SELECT *
FROM left_table
FULL OUTER JOIN right_table ON left_table.id = right_table.id;

Practical Example

Let’s consider two tables, employees and departments.

Table: employees

employee_id	employee_name	department_id
1	John Doe	1
2	Jane Smith	2
3	Mark Johnson	3
4	Sarah Brown	NULL

Table: departments

department_id	department_name
1	IT
2	HR
3	Marketing

Example: LEFT OUTER JOIN

SELECT *
FROM employees
LEFT OUTER JOIN departments ON employees.department_id = departments.department_id;

Explanation

• The LEFT OUTER JOIN combines rows from the employees table with the matching rows from the departments table based on the department_id.
• All rows from the left table (employees) are included in the result, even if there is no matching department in the right table (departments).
• If there is no match, NULL values are returned for columns from the right table.

Output:

employee_id	employee_name	department_id	department_name
1	John Doe	1	IT
2	Jane Smith	2	HR
3	Mark Johnson	3	Marketing
4	Sarah Brown	NULL	NULL

RIGHT OUTER JOIN

SELECT *
FROM employees
RIGHT OUTER JOIN departments ON employees.department_id = departments.department_id;

Output:

employee_id	employee_name	department_id	department_name
1	John Doe	1	IT
2	Jane Smith	2	HR
3	NULL	NULL	Marketing
4	NULL	NULL	NULL

The RIGHT OUTER JOIN returns all records from the right table (departments) and the matched records from the left table (employees).

3. FULL OUTER JOIN

SELECT *
FROM employees
FULL OUTER JOIN departments ON employees.department_id = departments.department_id;

Output:

employee_id	employee_name	department_id	department_name
1	John Doe	1	IT
2	Jane Smith	2	HR
3	Mark Johnson	3	Marketing
4	Sarah Brown	NULL	NULL
NULL	NULL	NULL	Finance

The FULL OUTER JOIN returns all records when there is a match in either the left or right table, ensuring that no data is left behind