🔁 SQL `FULL OUTER JOIN` – Combine All Records from Two Tables

A FULL OUTER JOIN in SQL lets you retrieve all rows from both tables, whether they match or not. When there’s no match, NULL is used to fill in missing values.

If you’re working with datasets where you want to preserve everything from both sources, FULL OUTER JOIN is your go-to SQL tool.

📘 What is SQL FULL OUTER JOIN?

A FULL OUTER JOIN (or FULL JOIN) combines the effects of:

✅ LEFT JOIN: All rows from the left table
✅ RIGHT JOIN: All rows from the right table
➕ Matching rows are joined together
❌ Non-matching rows from either side are filled with NULL

🧾 Syntax of SQL FULL OUTER JOIN

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

table1: The left table
table2: The right table
ON: The join condition

✅ Example: Students and Exam Results

Imagine you have two tables:

students

student_id	student_name
1	Alice
2	Bob
3	Carol

exam_results

student_id	score
2	85
3	92
4	78

You want to list all students and all exam results, whether or not they match.

SELECT 
  s.student_name,
  r.score
FROM students s
FULL OUTER JOIN exam_results r
ON s.student_id = r.student_id;

🧾 Result:

student_name	score
Alice	NULL
Bob	85
Carol	92
NULL	78

🎯 You see unmatched records from both tables, filled with NULL.

🔍 Use Cases for FULL OUTER JOIN

📊 Combine two data sources while preserving all data
❓ Identify missing records from either table
🧾 Perform full reconciliation between two datasets
🧮 Analyze symmetric differences between tables

📌 SQL FULL OUTER JOIN vs LEFT/RIGHT JOIN

Feature	LEFT JOIN	RIGHT JOIN	FULL OUTER JOIN
Includes all left rows	✅	❌	✅
Includes all right rows	❌	✅	✅
Matches rows	✅	✅	✅
Preserves unmatched	From left only	From right only	From both sides

🧪 Advanced FULL JOIN Example: Customers vs Orders

SELECT 
  c.customer_name,
  o.order_id
FROM customers c
FULL OUTER JOIN orders o
ON c.customer_id = o.customer_id;

🎯 Use this to:

Find customers with no orders
Identify orders with missing customer records

⚠️ Tips & Tricks for Using FULL OUTER JOIN

⚠️ Common Pitfall	✅ Best Practice
Forgetting to handle NULLs	Use `COALESCE()` or `IS NULL`
Poor performance on large data	Index join keys and filter early
Hard to interpret missing data	Label unmatched data clearly

💬 Combine FULL JOIN with WHERE

To find only unmatched rows from both sides (non-intersecting):

SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.id = table2.id
WHERE table1.id IS NULL OR table2.id IS NULL;

🎯 This pattern is perfect for data validation and missing value checks.

📝 Summary

FULL OUTER JOIN returns all rows from both joined tables
Matched rows appear as combined; unmatched rows are padded with NULL
It’s ideal for reporting, data audits, and comparisons

Full Outer Join

🔁 SQL FULL OUTER JOIN – Combine All Records from Two Tables