🔄 SQL LEFT JOIN – Include All Rows From the Left Table (Even Without Matches)

The LEFT JOIN in SQL is used to return all records from the left table, and the matched records from the right table.
If there’s no match, the result is still returned—but with NULL in the right table’s columns.

---

📘 What is SQL LEFT JOIN?

A LEFT JOIN (also known as LEFT OUTER JOIN) combines two tables and returns:

• ✅ All rows from the left table
• ✅ Matching rows from the right table
• ❌ If there is no match, it returns NULL for the right table columns

---

🧾 Syntax of LEFT JOIN in SQL

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

📌 table1 is your primary (left) table.
📌 table2 is your secondary (right) table.

---

✅ Real-Life Example: Customers & Orders

Suppose you have:

customers

customer_id	customer_name
1	Alice
2	Bob
3	Carol

orders

order_id	customer_id
101	1
102	2

Now run this query:

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

🧾 Result:

customer_name	order_id
Alice	101
Bob	102
Carol	NULL

🎯 Even though Carol has no order, her record still shows up.

---

🧪 Use Cases of LEFT JOIN

• Show all employees, even those not assigned to any department
• List all students, even if they haven’t enrolled in any course
• Display all products, even if they haven’t been sold
• Audit mismatches between records in two systems

---

✅ LEFT JOIN vs INNER JOIN

Feature	INNER JOIN	LEFT JOIN
Returns	Only matched rows	All left table rows + matches
No Match Behavior	Excludes non-matching rows	Includes left table rows with NULL
Use Case	Strict relationship needed	Optional/missing data matters

---

🔍 More LEFT JOIN Examples

1️⃣ Show all employees and their department (if any)

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;

2️⃣ List products and the category (even uncategorized ones)

SELECT p.product_name, c.category_name
FROM products p
LEFT JOIN categories c
ON p.category_id = c.id;

---

⚠️ Common Mistakes to Avoid

❌ Mistake	✅ Fix
Forgetting the ON clause	Always specify the condition to join tables
Assuming LEFT JOIN = FULL OUTER JOIN	LEFT JOIN only returns all left rows
Misunderstanding NULL values	Learn how NULL behaves in filtering

---

💡 Pro Tip: Combine LEFT JOIN with IS NULL

You can find non-matching records easily:

SELECT c.customer_name
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

🎯 This returns customers with no orders.

---

🧠 Practice Task

Challenge: List all employees and show their assigned project name. If no project is assigned, display NULL.

SELECT e.employee_name, p.project_name
FROM employees e
LEFT JOIN projects p
ON e.project_id = p.project_id;

---

📝 Summary

• LEFT JOIN includes all rows from the left table
• Returns NULL for the right table’s data when no match exists
• Very useful when you want to include unmatched data