SQL FULL OUTER JOIN

A FULL OUTER JOIN in SQL combines rows from two tables based on a join condition, but unlike an INNER JOIN, it returns all rows from *both* tables. If a row in one table doesn't have a match in the other, the result still includes that row, using NULL values for columns from the table where there's no match.

FULL OUTER JOIN: Definition and Usage

Use a FULL OUTER JOIN when you need a complete picture of data from both tables, even if some rows in one table don't have corresponding matches in the other. This is extremely valuable for identifying missing data or relationships between tables.

Syntax

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;
      

Note: FULL OUTER JOIN and FULL JOIN are functionally equivalent.

Caution: FULL OUTER JOIN can potentially return a very large result set if there are many unmatched rows in either of the tables.

Example Tables

We will use a simplified version of the well-known Northwind sample database. Here are excerpts from the 'Customers' and 'Orders' tables:

Customers Table

Orders Table

Example: FULL OUTER JOIN

This query combines data from the 'Customers' and 'Orders' tables using a FULL OUTER JOIN. It will return all customers and all orders, showing NULL values where there isn't a match in the other table.

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

CustomerName             | OrderID
---------------------------------
Alfreds Futterkiste      | NULL
Ana Trujillo Emparedados y helados | 10308
Antonio Moreno Taquería | NULL
(Additional rows would appear here, showing all customers and orders, with NULLs where there's no match)