What is a full outer join?
A FULL OUTER JOIN is a type of join in SQL that returns all rows from both the left and right tables, including rows that have matches in the other table, as well as rows that do not have matches. It essentially combines the results of both a LEFT OUTER JOIN and a RIGHT OUTER JOIN.
What is a Full Outer Join?
The FULL OUTER JOIN keyword returns all records when there is a match in either the left (table1) or the right (table2) table records. This means it will return all rows from the left table, all rows from the right table, and if there are rows that don't have a match in the other table, those rows will still be included in the result set.
For rows that do not have a match in the other table, the columns from the non-matching side will contain NULL values. This ensures that no data from either table is lost, providing a comprehensive view of all records from both datasets.
Syntax
SELECT column_list
FROM TableA
FULL OUTER JOIN TableB
ON TableA.matching_column = TableB.matching_column
WHERE condition;Example Scenario
Let's consider two tables: Customers and Orders. We want to see all customers and all orders, including customers who haven't placed any orders, and orders that might not be linked to any existing customer (though less common in a real-world scenario, it illustrates the join behavior).
Customers Table
| CustomerID | CustomerName |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
| 5 | Eve |
Orders Table
| OrderID | CustomerID | OrderAmount |
|---|---|---|
| 101 | 1 | 150.00 |
| 102 | 2 | 200.00 |
| 103 | 1 | 50.00 |
| 104 | 4 | 300.00 |
SQL Query
SELECT
C.CustomerID,
C.CustomerName,
O.OrderID,
O.OrderAmount
FROM
Customers C
FULL OUTER JOIN
Orders O ON C.CustomerID = O.CustomerID;Result of FULL OUTER JOIN
| CustomerID | CustomerName | OrderID | OrderAmount |
|---|---|---|---|
| 1 | Alice | 101 | 150.00 |
| 1 | Alice | 103 | 50.00 |
| 2 | Bob | 102 | 200.00 |
| 3 | Charlie | NULL | NULL |
| 5 | Eve | NULL | NULL |
| NULL | NULL | 104 | 300.00 |
Key Characteristics
- Returns all rows from both the left and right tables.
- Includes matched rows where the join condition is true.
- Includes unmatched rows from the left table, with NULLs for right table columns.
- Includes unmatched rows from the right table, with NULLs for left table columns.
- It provides a complete picture of all data from both tables.
When to Use a Full Outer Join
A FULL OUTER JOIN is useful when you need to see all information from two related tables, even if there isn't a direct match between them. Common use cases include:
- Data Reconciliation: Comparing two datasets to find discrepancies or missing entries on either side.
- Auditing: Ensuring all records are accounted for across different systems or stages.
- Reporting: Generating reports that require a comprehensive view of all possible entities, regardless of their association status.