⚪ SQL FULL JOIN – Combine Matched and Unmatched Rows

🧲 Introduction – What is SQL FULL JOIN?

A FULL JOIN (or FULL OUTER JOIN) combines all rows from both tables, returning matched rows and also unmatched rows with NULL where no match exists.

🎯 In this guide, you’ll learn:

• Syntax and usage of FULL JOIN
• How it compares with INNER, LEFT, and RIGHT JOINs
• Workarounds for databases that don’t support it natively

---

✅ 1. FULL JOIN Syntax

SELECT columns
FROM table1
FULL JOIN table2 ON table1.key = table2.key;

✅ Returns rows from both tables—even if there is no match in the other.

---

📥 2. Example – Employees and Projects

SELECT e.name AS employee, p.name AS project
FROM employees e
FULL JOIN projects p ON e.project_id = p.id;

✅ Includes:

• Employees with projects
• Projects without employees
• Employees not assigned to any project

---

🔄 3. MySQL Workaround – FULL JOIN with UNION

SELECT *
FROM a
LEFT JOIN b ON a.id = b.id
UNION
SELECT *
FROM a
RIGHT JOIN b ON a.id = b.id;

✅ MySQL doesn’t support FULL JOIN directly—this emulates it.

---

🔁 4. FULL JOIN vs Other Joins

Type	Matches from A	Matches from B	Unmatched A	Unmatched B
INNER JOIN	✅	✅	❌	❌
LEFT JOIN	✅	✅	✅	❌
RIGHT JOIN	✅	✅	❌	✅
FULL JOIN	✅	✅	✅	✅

---

🔍 5. NULL Awareness in FULL JOIN

SELECT c.name, o.id
FROM customers c
FULL JOIN orders o ON c.id = o.customer_id
WHERE c.id IS NULL OR o.id IS NULL;

✅ Returns unmatched records only (i.e., anti-join use case).

---

📘 Best Practices

✅ Recommended	❌ Avoid This
Use FULL JOIN for completeness reports	Using on large tables without filtering
Always handle NULL values properly	Assuming all rows are matched
Use UNION workaround in MySQL	Expecting native FULL JOIN in all RDBMS

---

📌 Summary – Recap & Next Steps

FULL JOIN is powerful when you need a complete view of data, including unmatched rows from either side. It’s ideal for comparisons, synchronization, and gap analysis.

🔍 Key Takeaways:

• Combines results of LEFT JOIN and RIGHT JOIN
• Returns all rows with NULL where no match exists
• Simulate with UNION if unsupported

⚙️ Real-World Relevance:
Used in reporting tools, change-tracking queries, and completeness audits.

➡️ Next: Explore CROSS JOIN for full combinations, or CASE for column-level conditional logic.

---

❓ FAQ – SQL FULL JOIN

❓ What does FULL JOIN return?

✅ All rows from both tables with NULLs where there’s no match.

❓ Is FULL JOIN supported in MySQL?

❌ No. Use UNION of LEFT and RIGHT JOIN instead.

❓ What is the difference between FULL JOIN and INNER JOIN?

✅ INNER JOIN only returns matched rows; FULL JOIN returns all.

❓ Does FULL JOIN affect performance?

✅ Yes. It’s more intensive—filter or index wisely.

❓ When should I use FULL JOIN?

✅ When you need all data—even unmatched—from both sides.

---

Share Now :