iwantcoding.com
🔥 Daily 👥 Rooms 🏆 Top Log in Sign up

SQL FULL JOIN

By · Reviewed & last updated · Part of SQL Tutorial

FULL OUTER JOIN keeps every row from both sides. Rows with no match get NULL on the missing side.

Example

SQL
SELECT c.name, o.total
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;

What it returns

ScenarioAppears?
Customer with ordersYes — one row per order.
Customer without ordersYes — total is NULL.
Order with no matching customerYes — name is NULL.

Vendor support

  • SQL Server, PostgreSQL, Oracle, SQLite 3.39+ — supported.
  • MySQL doesn't have FULL OUTER JOIN. Emulate it with LEFT JOIN UNION RIGHT JOIN.

MySQL workaround

SQL
SELECT c.name, o.total FROM customers c LEFT  JOIN orders o ON o.customer_id = c.id
UNION
SELECT c.name, o.total FROM customers c RIGHT JOIN orders o ON o.customer_id = c.id;
Tip: A FULL OUTER JOIN often signals a reconciliation problem — "show me everything in either source, side by side". Common in finance, audit, and migration scripts.

Example

Example
SELECT c.name, o.total
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;
Try it Yourself »

Exercise

Returns every row from both tables.

FULL JOIN orders ON ...

Test yourself

Q1. FULL OUTER JOIN returns rows…
Q2. Which DB does NOT natively support FULL OUTER JOIN?
Q3. MySQL emulates FULL OUTER JOIN with…

Discussion

Loading…