FULL OUTER JOIN

You’ve learned INNER JOIN (which requires a match in both tables) and LEFT JOIN (which keeps everything from the first table).

But what if you want to keep absolutely everything from both tables, regardless of whether they match or not? You use a FULL OUTER JOIN.

How FULL OUTER JOIN Works

A FULL OUTER JOIN combines the results of both a LEFT JOIN and a RIGHT JOIN. It returns all rows from the left table and all rows from the right table. Where the tables match, the data is joined on the same row. Where they do not match, the database inserts NULL values for the missing data on either side.

SELECT products.name, current_inventory.quantity
FROM products
FULL OUTER JOIN current_inventory
  ON products.id = current_inventory.product_id;

Let’s look at what this would return using our lab schema:

Laptop (Matches in both): Returns ‘Laptop’ and ‘5’.
Mouse (Matches in both): Returns ‘Mouse’ and ‘10’.
Keyboard (In products, but missing from inventory): Returns ‘Keyboard’ and NULL.
Product 999 (In inventory, but missing from products): Returns NULL and ‘50’.

When to use FULL OUTER JOIN

In practice, FULL OUTER JOIN is quite rare. You will use INNER and LEFT joins 95% of the time.

However, FULL OUTER JOIN is extremely useful for:

Data Auditing: Finding mismatches, orphaned records, and data corruption between two systems.
Merging Datasets: Combining two similar tables from different databases into a single cohesive list.

[!CAUTION] Not all database engines support FULL OUTER JOIN. PostgreSQL and SQL Server do. MySQL and SQLite do not. In MySQL/SQLite, you have to write a LEFT JOIN, write a RIGHT JOIN, and combine the two results using a UNION clause (which we will cover later).

For our interactive lab, we are using a database engine that supports it. Try running a FULL OUTER JOIN to help the audit team find the mismatched data!