What is the difference between INNER JOIN, OUTER JOIN and FULL OUTER JOIN?

Problem

What is the difference between INNER JOIN, OUTER JOIN and FULL OUTER JOIN?

Input

Let’s create three example tables, employees, departments, and projects, and insert some sample data into them.

SELECT * FROM EMPLOYEES;

employee_id	first_name	last_name	department_id
1	John	Doe	1
2	Jane	Smith	2
3	Bob	Johnson	1
4	Alice	Williams

SELECT * FROM DEPARTMENT;

department_id	department_name
1	HR
2	IT
3	Finance

SELECT * FROM PROJECTS;

project_id	project_name	department_id
101	Employee Training	1
102	Database Upgrade	2
103	Budget Analysis	3

Try Hands-On: Fiddle

Create Input Table: Gist

What is the difference between Inner, Outer and Full Outer Join?

In SQL, INNER JOIN, OUTER JOIN (LEFT OUTER JOIN or RIGHT OUTER JOIN), and FULL OUTER JOIN are used to combine rows from two or more tables based on a related column between them. The difference lies in which rows are included in the result set.

Inner Join:

INNER JOIN returns only the rows that are present in BOTH tables being joined. It filters out rows from both tables that do not meet the join condition.

Outer Join:

OUTER JOIN returns all rows from one table and the matching rows from another table. It includes rows from one table even if there is no match in the other table. There are two types of OUTER joins: LEFT OUTER JOIN and RIGHT OUTER JOIN.

Left Outer join keeps all rows from the left side table in the JOIN statement. Same logic applies for Right Outer Join, but all the rows from Right side table.

Full Outer Join:

FULL OUTER JOIN returns all rows from both tables, including matching and non-matching rows. If there is no match in one of the tables, NULL values are included for columns from the non-matching table.

Effectively, Full Outer join will NOT leave out any row from both tables.

INNER JOIN Example

-- INNER JOIN to retrieve employees and their department names
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

Result: Rows that are present in both tables are returned.

first_name	last_name	department_name
John	Doe	HR
Jane	Smith	IT
Bob	Johnson	HR

LEFT OUTER JOIN Example

-- LEFT OUTER JOIN to retrieve all employees and their department names (including employees with no department)
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT OUTER JOIN departments ON employees.department_id = departments.department_id;

Result: ALL rows present in the left table (employees) is returned.

first_name	last_name	department_name
John	Doe	HR
Jane	Smith	IT
Bob	Johnson	HR
Alice	Williams

If there is a match with the department table, that is if the department_id from employees table is present in the departments table, then the department name is shown. If there is no match, the department name will be NULL.

FULL OUTER JOIN Example

-- FULL OUTER JOIN to retrieve all employees and their department names (including employees with no department) and all projects
SELECT employees.first_name, employees.last_name, departments.department_name, projects.project_name
FROM employees
FULL OUTER JOIN departments ON employees.department_id = departments.department_id
FULL OUTER JOIN projects ON employees.department_id = projects.department_id;

In this example, all employees are included, along with their department names and project names.

If there is no match in a table (e.g., an employee with no department or a project with no department), NULL values will be included.

Explanation

• INNER JOIN returns only matching rows from both tables.

• LEFT OUTER JOIN returns all rows from the left table (employees) and the matching rows from the right table (departments), filling in NULLs for non-matching rows in the right table.

• FULL OUTER JOIN returns all rows from both tables, including matching and non-matching rows, filling in NULLs for columns from the non-matching table.

first_name	last_name	department_name	project_name
John	Doe	HR	Employee Training
Jane	Smith	IT	Database Upgrade
Bob	Johnson	HR	Employee Training
Alice	Williams
		Finance
			Budget Analysis

The FULL OUTER JOIN statement does not work with MySQL. So, to implement this, we need to use a different approach using UNION.

Full Outer Join in MySQL

sql
-- FULL OUTER JOIN using UNION of LEFT JOIN and RIGHT JOIN
SELECT employees.first_name, employees.last_name, departments.department_name, projects.project_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id
UNION
SELECT employees.first_name, employees.last_name, departments.department_name, projects.project_name
FROM departments
RIGHT JOIN employees ON employees.department_id = departments.department_id

This query first performs a LEFT JOIN between the employees and departments tables to get all employees and their corresponding department names.

Then, it performs a RIGHT JOIN between the departments and employees tables to get all departments and their corresponding employee names.

Finally, it combines the results using the UNION clause to simulate a FULL OUTER JOIN.
The a result includes all employees, their department names (if available), all departments, and their employee names (if available)d.

Recommended Courses

1. SQL for Data Science – Level 1
2. SQL for Data Science – Level 2
3. SQL for Data Science – Level 3

Recommended Tutorial

1. Introduction to SQL
2. SQL Window Functons – Made Simple and Easy
3. SQL Subquery

More SQL Questions

1. How to efficiently convert rows to columns in SQL?
2. How to transpose columns to rows in SQL?
3. How to select first row in each GROUP BY group?