SQL Full Join

What FULL JOIN does

A FULL JOIN (also written FULL OUTER JOIN) combines the behavior of the left and right joins. Every row from both tables is guaranteed to appear. Where a row on one side matches a row on the other, they are joined together. Where a row has no match, it still appears, with the columns from the other table filled with NULL. This gives you the complete picture, including records that exist on only one side.

SELECT Customers.CustomerName, Orders.OrderID, Orders.Amount
FROM Customers
FULL JOIN Orders
  ON Customers.CustomerID = Orders.CustomerID;

The result contains matched pairs (Anita Rao's two orders and Ben Carter's one), customers with no orders (Chloe Dubois and Diego Alvarez, with NULL order data), and orders with no customer (order 104, with a NULL customer name). Nothing from either table is left out.

When a full join is the right choice

Reach for a FULL JOIN when you need to reconcile two data sets and must see everything, including mismatches on either side. Filtering for rows where either key is NULL is a quick way to audit both orphaned orders and inactive customers in a single query.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL JOIN Orders
  ON Customers.CustomerID = Orders.CustomerID
WHERE Customers.CustomerID IS NULL
   OR Orders.CustomerID IS NULL;

• A FULL JOIN keeps unmatched rows from both tables.
• Missing values on either side are filled with NULL.
• It is essentially a LEFT JOIN and a RIGHT JOIN merged into one result.
• Use it for reconciliation and data-quality checks across two tables.