FULL OUTER JOIN vs. FULL JOIN

FULL OUTER JOIN vs. FULL JOIN: Understanding SQL's Complete Data Retrieval

Explore the nuances of FULL OUTER JOIN and FULL JOIN in SQL, understanding their identical functionality and how they retrieve all matching and non-matching rows from two tables.

In SQL, retrieving data from multiple tables is a fundamental operation, and JOIN clauses are at the heart of this process. Among the various JOIN types, FULL OUTER JOIN (often simply referred to as FULL JOIN) stands out for its ability to combine all rows from both the left and right tables, returning NULL values for columns where no match is found. This article delves into the functionality of FULL OUTER JOIN and FULL JOIN, clarifying their identical nature and demonstrating their practical application.

The Identity of FULL OUTER JOIN and FULL JOIN

A common point of confusion for SQL beginners is the distinction between FULL OUTER JOIN and FULL JOIN. The simple truth is: there is no distinction. These two terms are synonymous and refer to the exact same operation in SQL. The OUTER keyword is optional when specifying a FULL join, much like it is optional for LEFT and RIGHT joins (e.g., LEFT JOIN is shorthand for LEFT OUTER JOIN).

Both FULL OUTER JOIN and FULL JOIN produce a result set that includes all rows from the left table and all rows from the right table. Where a row from one table does not have a matching row in the other table, the columns from the non-matching table will contain NULL values. This type of join is particularly useful when you need to see all records from both datasets, regardless of whether they have a corresponding entry in the other.

erDiagram
    CUSTOMER ||--o{ ORDER : places
    CUSTOMER { 
        int CustomerID PK
        varchar Name
    }
    ORDER { 
        int OrderID PK
        int CustomerID FK
        varchar OrderDate
    }

Entity-Relationship Diagram for Customer and Order tables

How FULL OUTER JOIN / FULL JOIN Works

Let's consider two tables: Customers and Orders. The Customers table contains CustomerID and CustomerName, while the Orders table contains OrderID, CustomerID, and OrderDate. A FULL OUTER JOIN between these two tables on CustomerID would yield the following:

1. Matching Rows

For every CustomerID that exists in both Customers and Orders, a single row will be returned containing data from both tables.

2. Non-Matching Customers

For every CustomerID in Customers that does not have a corresponding OrderID in Orders (i.e., a customer who has not placed any orders), a row will be returned with the Customer data and NULL values for all Order columns.

3. Non-Matching Orders

For every OrderID in Orders that does not have a corresponding CustomerID in Customers (e.g., an order with an invalid or deleted customer ID), a row will be returned with the Order data and NULL values for all Customer columns.

Practical Example in SQL

Let's illustrate with a concrete SQL example using hypothetical Customers and Orders tables. We'll create some sample data to demonstrate all three scenarios described above.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(50)
);

INSERT INTO Customers (CustomerID, CustomerName)
VALUES
    (1, 'Alice'),
    (2, 'Bob'),
    (3, 'Charlie');

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE
);

INSERT INTO Orders (OrderID, CustomerID, OrderDate)
VALUES
    (101, 1, '2023-01-15'),
    (102, 1, '2023-02-20'),
    (103, 2, '2023-03-10'),
    (104, 99, '2023-04-05'); -- Order with non-existent CustomerID

SELECT
    C.CustomerID,
    C.CustomerName,
    O.OrderID,
    O.OrderDate
FROM
    Customers C
FULL OUTER JOIN
    Orders O ON C.CustomerID = O.CustomerID;

SQL script demonstrating FULL OUTER JOIN with sample data.

The output of the above query would look something like this:

Result set from the FULL OUTER JOIN query.

Notice how Charlie (CustomerID 3) appears with NULL values for OrderID and OrderDate because he has no orders. Conversely, the order with OrderID 104 appears with NULL values for CustomerID and CustomerName because its CustomerID (99) does not exist in the Customers table. Alice and Bob appear with their respective orders, demonstrating the matching rows.