# 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
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
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.

Course Preview