sql_join_full

SQL FULL OUTER JOIN Keyword

Understanding SQL FULL OUTER JOIN

The FULL OUTER JOIN (or FULL JOIN in some databases) keyword is used to retrieve all rows from both the left table (table1) and the right table (table2). It combines the results of both LEFT JOIN and RIGHT JOIN.

If there is a match between the tables on the join condition, the columns from both tables are included in the result row. If a row in one table does not have a matching row in the other table (based on the join condition), the result set will still include that row, but with NULL values for the columns from the table where no match was found.

SQL FULL OUTER JOIN Syntax

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.join_column = table2.join_column
WHERE condition; -- Optional: further filter results

Note: Some database systems, like older versions of MySQL, do not support FULL OUTER JOIN directly. In such cases, you can achieve a similar result using a UNION ALL of a LEFT JOIN and a RIGHT JOIN (carefully excluding the intersection to avoid duplicates if UNION is used instead of UNION ALL with a specific WHERE clause for the right join part).

Demo Database: Northwind

For our examples, we’ll use the classic Northwind sample database. Imagine we have two key tables: Customers and Orders.

A simplified view of the “Customers” table:

CustomerID | CustomerName                      | Country
-----------|-----------------------------------|---------
1          | Alfreds Futterkiste             | Germany
2          | Ana Trujillo Emparedados y helados| Mexico
3          | Antonio Moreno Taquería         | Mexico
4          | Around the Horn                   | UK        -- Example customer without orders
...

A simplified view of the “Orders” table:

OrderID | CustomerID | OrderDate
--------|------------|-----------
10308   | 2          | 1996-09-18
10309   | 37         | 1996-09-19  -- Example order for a customer not in the above snippet
10310   | 77         | 1996-09-20
...

SQL FULL OUTER JOIN Example

The following SQL statement selects all customers and all orders. If a customer has no orders, their order details will be NULL. If an order belongs to a customer not in our selected customer list (or an orphaned order), its customer details will be NULL.

SELECT c.CustomerName, o.OrderID
FROM Customers c
FULL OUTER JOIN Orders o ON c.CustomerID = o.CustomerID
ORDER BY c.CustomerName;

A selection from the result set might look like this:

CustomerName                      | OrderID
-----------------------------------|---------
Alfreds Futterkiste             | 10643   -- Assuming Alfreds has this order
Alfreds Futterkiste             | 10692   -- And this one
Ana Trujillo Emparedados y helados| 10308
Antonio Moreno Taquería         | 10365
Around the Horn                   | NULL    -- Customer with no orders
NULL                              | 10309   -- Order whose customer is not in Customers or not matched
...

Key Takeaway: FULL OUTER JOIN ensures that every row from both participating tables is represented in the result, using NULLs to fill in gaps where matches don’t exist. This is useful for identifying records in one table that don’t have corresponding records in another, and vice-versa.

Common Use Cases:

• Data reconciliation between two datasets.
• Finding orphaned records or records without corresponding entries in related tables.
• Generating comprehensive reports that include all entities from both sides of a relationship.