FULL OUTER JOIN

Introduction

In SQL, the FULL OUTER JOIN is used to return all records when there is a match in either left or right table. This type of join combines the results of both LEFT JOIN and RIGHT JOIN, ensuring that every record from both tables is included in the result set, even if there are no matching records.

Concept

A FULL OUTER JOIN returns rows when there is a match in one of the tables. If there is no match, the result is NULL on the side where there is no match. This means that all records from both tables will be included in the result set, with NULL values filling in for missing matches.

The syntax for a FULL OUTER JOIN is as follows:

SELECT column1, column2, ...
FROM table1
FULL OUTER JOIN table2
ON table1.common_column = table2.common_column;

Examples

Let's explore some practical examples to understand how FULL OUTER JOIN works.

Example 1: Basic Usage

Suppose we have two tables, employees and departments, and we want to retrieve all employees along with their department names, even if some employees do not belong to any department.

Tables Structure

employees

departments

SQL Query

SELECT employees.employee_name, departments.department_name
FROM employees
FULL OUTER JOIN departments
ON employees.department_id = departments.department_id;

Output

| employee_name | department_name |
|---------------|-----------------|
| Alice         | HR              |
| Bob           | NULL            |
| Charlie       | IT              |
| NULL          | Finance         |

In this example, Alice and Charlie are matched with their respective departments (HR and IT). Bob does not have a department, so his department name is NULL. Similarly, the Finance department has no employees, so its employee name is NULL.

Example 2: Handling NULL Values

Let's modify the previous example to include additional columns and handle NULL values more explicitly.

Tables Structure

employees

departments

SQL Query

SELECT 
    employees.employee_name, 
    departments.department_name,
    COALESCE(employees.salary, 0) AS salary,
    COALESCE(departments.budget, 0) AS budget
FROM employees
FULL OUTER JOIN departments
ON employees.department_id = departments.department_id;

Output

| employee_name | department_name | salary | budget |
|---------------|-----------------|--------|--------|
| Alice         | HR              | 5000   | 100000 |
| Bob           | NULL            | 4500   | 0      |
| Charlie       | IT              | 6000   | 80000  |
| NULL          | Finance         | 0      | 120000 |

In this example, we use the COALESCE function to handle NULL values by replacing them with 0. This makes it easier to understand and work with the data.

What's Next?

Now that you have a good understanding of FULL OUTER JOIN, you might want to explore more complex queries involving subqueries. Subqueries allow you to include the results of one query within another, providing powerful ways to filter and manipulate your data.

Stay tuned for more tutorials on SQL advanced topics!