SQL FULL OUTER JOIN

What is SQL FULL OUTER JOIN?

SQL FULL OUTER JOIN is a type of SQL JOIN operation that retrieves all records from both tables, including matching and non-matching records. If there are no matching records in one or both tables, NULL values are included for those columns. FULL OUTER JOINs provide a comprehensive view of data from multiple tables, similar to OUTER JOIN, but it ensures that no records are omitted from either table.

When you would use it

You would use SQL FULL OUTER JOIN when you want to retrieve all records from both tables, regardless of whether there are matching records. Common use cases for FULL OUTER JOIN include:

1. Merging data: When you need to combine data from two tables into a single result set while preserving all records.

2. Handling missing data: In scenarios where data might be missing or incomplete in one or both tables.

3. Comparing data: For data analysis, auditing, or quality control to compare and identify differences between two data sources.

4. Reporting exceptions: To identify and report data discrepancies or anomalies across tables.

Syntax

The syntax for a SQL FULL OUTER JOIN operation is as follows:

SELECT column_list
FROM table1
FULL OUTER JOIN table2 ON table1.column = table2.column;

• column_list: A list of columns to retrieve from the joined tables.
• table1 and table2: The names of the tables to be joined.
• column: The common column or key that relates the tables.

Parameter values

• column_list: A list of column names you want to select from the joined tables.
• table1 and table2: The names of the tables to be joined.
• column: The related column or key that connects the tables.

Example query

Suppose you have two tables, "employees" and "departments," and you want to retrieve a list of all employees and their corresponding departments, including departments with no employees and employees with no assigned departments. You can use a SQL FULL OUTER JOIN like this:

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

Example table response

Assuming the "employees" and "departments" tables contain the following data:

employees:

| employee_id | employee_name | department_id |
| ----------- | ------------- | ------------- |
| 1           | John Smith    | 101           |
| 2           | Mary Johnson  | 102           |
| 3           | Sam Brown     | 103           |

departments:

| department_id | department_name |
| ------------- | --------------- |
| 101           | HR              |
| 102           | Finance         |
| 104           | Marketing        |

The query mentioned earlier would return the following result:

| employee_id | employee_name | department_name |
| ----------- | ------------- | --------------- |
| 1           | John Smith    | HR              |
| 2           | Mary Johnson  | Finance         |
| 3           | Sam Brown     | NULL            |
| NULL        | NULL          | Marketing        |

This result combines data from both tables, displaying all employees and departments. In this case, the employee with employee_id 3 has no corresponding department, and the department with department_id 104 has no corresponding employees.

Use cases

• Retrieving all records from both tables, including matching and non-matching records.
• Handling optional or missing data scenarios.
• Combining data while preserving all records.
• Comparing and auditing data from multiple sources.

SQL languages this is available for

SQL FULL OUTER JOIN is a standard SQL feature and is available in most relational database management systems (RDBMS), including but not limited to:

• MySQL
• PostgreSQL
• Oracle Database
• SQL Server
• SQLite
• IBM Db2
• MariaDB

The syntax and behavior of FULL OUTER JOIN are consistent across these database systems.