🔸 SQL LEFT JOIN – Include All Left Rows with Examples

🧲 Introduction – What is SQL LEFT JOIN?

A LEFT JOIN returns all records from the left table and the matched records from the right table. If there’s no match, the result from the right side is NULL.

🎯 In this guide, you’ll learn:

• Syntax and structure of LEFT JOIN
• Use cases with real examples
• Key differences from INNER JOIN and RIGHT JOIN

---

✅ 1. LEFT JOIN Syntax

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

✅ Ensures that every row from the left table appears in the result.

---

🧾 2. Example – Customers with or without Orders

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

✅ Shows all customers, even those who haven’t placed any orders.

---

🧩 3. LEFT JOIN vs INNER JOIN

-- INNER JOIN excludes non-matching rows
-- LEFT JOIN includes all rows from the left table

✅ Use LEFT JOIN when you need to preserve unmatched left rows.

---

🔄 4. LEFT JOIN with Filtering (Important!)

-- Correct way to filter on right-side columns:
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'pending';

⚠️ This behaves like an INNER JOIN because NULLs are excluded. To preserve unmatched left rows:

WHERE o.status = 'pending' OR o.status IS NULL

---

🔍 5. LEFT JOIN with Aggregates

SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

✅ Includes customers with zero orders.

---

📘 Best Practices

✅ Recommended	❌ Avoid This
Use LEFT JOIN to include unmatched data	Filtering right-side columns without handling NULL
Always test for NULL in the WHERE clause	Assuming LEFT JOIN behaves like INNER JOIN
Combine with aggregates for summary	Ignoring NULLs in analysis

---

📌 Summary – Recap & Next Steps

The LEFT JOIN is your go-to when you want to preserve all data from the left table, regardless of whether a match exists on the right.

🔍 Key Takeaways:

• Returns all rows from the left, matching from right
• NULLs fill gaps from unmatched right-side records
• Great for finding missing or optional data

⚙️ Real-World Relevance:
Used in reports, outer lookups, dashboards, and data completeness checks.

➡️ Next: Learn RIGHT JOIN for mirrored logic, or explore FULL JOIN for both sides.

---

❓ FAQ – SQL LEFT JOIN

❓ What is the purpose of LEFT JOIN?

✅ To return all rows from the left table, even if there are no matching rows in the right table.

❓ What fills unmatched columns from the right table?

✅ NULL values.

❓ How is LEFT JOIN different from RIGHT JOIN?

✅ LEFT JOIN keeps left table rows; RIGHT JOIN keeps right table rows.

❓ Can I use LEFT JOIN with multiple tables?

✅ Yes. Chain them just like multiple INNER JOINs.

---

Share Now :