RIGHT JOIN is the mirror of LEFT JOIN. FULL OUTER JOIN is the combination of both. Honestly, you will use LEFT JOIN 95% of the time. But you need to know the others for interviews and for that one edge case where they are the right tool.
RIGHT JOIN keeps all rows from the right table and fills NULLs for unmatched left-side rows. It is functionally identical to swapping the table order and using LEFT JOIN. Most teams prefer LEFT JOIN with swapped table positions because it reads more naturally — left to right, primary table first.
-- RIGHT JOIN: All orders, with customer info if available
SELECT
c.first_name,
c.last_name,
o.order_id,
o.total_amount
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;
-- This is equivalent to:
SELECT
c.first_name,
c.last_name,
o.order_id,
o.total_amount
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;
-- Both return the same result — all orders, NULLs where no customer matchFULL OUTER JOIN returns all rows from both tables. Matched rows get combined. Unmatched rows from either side get NULLs for the missing columns. It is the complete picture — nothing is lost.
-- FULL OUTER JOIN: See everything — matched and unmatched
SELECT
c.customer_id,
c.first_name,
o.order_id,
o.total_amount
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;
-- Result includes:
-- 1. Customers WITH orders (both sides filled)
-- 2. Customers WITHOUT orders (order columns = NULL)
-- 3. Orders WITHOUT customers (customer columns = NULL)
-- QA use case: Find ALL mismatches in a data migration
SELECT
old.record_id AS old_id,
new.record_id AS new_id,
old.value AS old_value,
new.value AS new_value
FROM old_system_data old
FULL OUTER JOIN new_system_data new ON old.record_id = new.record_id
WHERE old.record_id IS NULL -- exists in new but not old
OR new.record_id IS NULL -- exists in old but not new
OR old.value != new.value; -- values differMySQL does not support FULL OUTER JOIN directly. You need to simulate it with a UNION of LEFT JOIN and RIGHT JOIN. PostgreSQL, SQL Server, and Oracle support it natively.
| JOIN Type | Left Table Rows | Right Table Rows | Unmatched Handling |
|---|---|---|---|
| INNER JOIN | Only matched | Only matched | Dropped from both sides |
| LEFT JOIN | All kept | Only matched | Right side gets NULLs |
| RIGHT JOIN | Only matched | All kept | Left side gets NULLs |
| FULL OUTER JOIN | All kept | All kept | Both sides get NULLs where no match |
Q: When would you use FULL OUTER JOIN in a real project?
A: Data migration validation is the classic use case. You have data in the old system and the new system. FULL OUTER JOIN on the record ID lets you see: records that migrated correctly (matched), records missing from the new system (NULL on the right), and unexpected records in the new system (NULL on the left). It is the complete reconciliation tool.
Key Point: RIGHT JOIN is LEFT JOIN in reverse. FULL OUTER JOIN keeps all rows from both sides. Use FULL OUTER JOIN for data migration comparisons.