🔸 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 :
