Skip to content

03_LEFT_JOIN.md

Latest commit

 

History

History
146 lines (108 loc) · 5.49 KB

03_LEFT_JOIN.md

File metadata and controls

146 lines (108 loc) · 5.49 KB

⬅️ Topic 10.3: LEFT JOIN (The Complete Outer Join)

INNER JOIN only shows rows where both tables have a match. But what about customers who haven't placed any orders yet? Or employees who haven't been assigned a department? LEFT JOIN keeps everything from the left table — match or no match.

1. Definition

LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table (the one written before the LEFT JOIN keyword), and the matching rows from the right table. If there is no match in the right table, those columns appear as NULL in the result.

2. Why This Concept Exists

Real-world reporting constantly needs "complete lists with optional related data":

  • "Show me ALL employees — and their department name if they have one."
  • "Show me ALL products — and how many times they've been sold (0 if never)."
  • "Show me ALL students — and their grades (NULL if no grades recorded)."

INNER JOIN would silently DROP unmatched rows. LEFT JOIN keeps them visible.

3. How It Works — The NULL Padding (PRO LEVEL)

When MySQL processes a LEFT JOIN:

  1. It reads each row from the left table.
  2. It looks for matching rows in the right table.
  3. If a match is found: Combines both rows normally.
  4. If NO match is found: Returns the left row with NULL values for all right-table columns.
Customers (Left)        Orders (Right)
ID=1, Name=Ravi   →  Order found → combined row returned
ID=2, Name=Priya  →  NO orders  → Priya returned with NULL for all Order columns
ID=3, Name=Aditi  →  Order found → combined row returned

4. Syntax / Implementation — Full Cheat Sheet

-- Basic LEFT JOIN
SELECT c.Customer_Name, o.Order_ID, o.Total_Amount
FROM Customers c
LEFT JOIN Orders o ON c.Customer_ID = o.Customer_ID;
-- Result: All customers. Customers with no orders have NULL in Order_ID and Total_Amount.

-- Finding UNMATCHED rows (The classic "orphan finder" pattern)
SELECT c.Customer_Name
FROM Customers c
LEFT JOIN Orders o ON c.Customer_ID = o.Customer_ID
WHERE o.Order_ID IS NULL;
-- Shows ONLY customers who have placed ZERO orders.

-- LEFT JOIN with filter on LEFT table (safe — filters before join)
SELECT c.Customer_Name, o.Order_ID
FROM Customers c
LEFT JOIN Orders o ON c.Customer_ID = o.Customer_ID
WHERE c.City = 'Mumbai';  -- This is on the LEFT table — still works correctly!

5. The WHERE Clause Trap — Most Common Bug

This is the #1 mistake beginners make with LEFT JOIN:

-- ❌ WRONG — Accidentally converts LEFT JOIN to INNER JOIN!
SELECT c.Name, o.Status
FROM Customers c
LEFT JOIN Orders o ON c.Customer_ID = o.Customer_ID
WHERE o.Status = 'Paid';
-- The WHERE clause filters out NULL rows (customers with no orders).
-- Result: Behaves exactly like an INNER JOIN — all unmatched rows vanish!

-- ✅ CORRECT — Move the filter into the join condition
SELECT c.Name, o.Status
FROM Customers c
LEFT JOIN Orders o ON c.Customer_ID = o.Customer_ID AND o.Status = 'Paid';
-- Now: All customers appear. Only 'Paid' orders are joined. Others get NULL.

6. Real-Life Examples

Example 1 — Finding Inactive Customers (The IS NULL pattern):

-- Which customers have NEVER placed an order?
SELECT c.Customer_Name, c.Email
FROM Customers c
LEFT JOIN Orders o ON c.Customer_ID = o.Customer_ID
WHERE o.Order_ID IS NULL;
-- Marketing can target these customers with a "We miss you!" email

Example 2 — Product Sales Report with Zero-selling products:

SELECT 
    p.Product_Name,
    COUNT(oi.Order_ID) AS Times_Sold,
    COALESCE(SUM(oi.Quantity), 0) AS Total_Qty_Sold
FROM Products p
LEFT JOIN Order_Items oi ON p.Product_ID = oi.Product_ID
GROUP BY p.Product_ID, p.Product_Name
ORDER BY Total_Qty_Sold DESC;
-- Products never sold appear at the bottom with 0.

Example 3 — HR: All employees with optional bank account:

SELECT e.First_Name, e.Last_Name, b.Account_Number
FROM Employees e
LEFT JOIN Bank_Accounts b ON e.Emp_ID = b.Emp_ID;
-- Employees with no bank account registered: Account_Number = NULL (HR alert!)

7. Common Mistakes

  • The WHERE trap (above): Adding a filter on the right-table column in WHERE instead of ON. Always ask yourself: "Is this filter on the LEFT table or the RIGHT table?"
  • Table order confusion: A LEFT JOIN B gives ALL of A. B LEFT JOIN A gives ALL of B. They are NOT the same.
  • Using LEFT JOIN when you mean INNER JOIN: Don't default to LEFT JOIN for everything. Use INNER JOIN when you know both sides have matching data.

8. Tips & Best Practices

  • Index the right-table's join column. Since LEFT JOIN scans the right table for each left-table row, an index makes it dramatically faster.
  • Use COALESCE(col, default) to handle NULLs in output: Instead of showing bare NULL, display 0 or 'No data' using COALESCE(o.Total, 0).
  • "Find unmatched rows" with WHERE right_col IS NULL is often faster than NOT IN (subquery) on large datasets.

9. Mini Practice Tasks

  • Task 1: Write a query to find all Departments that have no employees assigned to them. Tables: Departments(Dept_ID, Dept_Name) and Employees(Emp_ID, Dept_ID).
  • Task 2: Fix this broken query so it correctly shows all customers even if their orders are not 'Shipped': SELECT c.Name FROM Customers c LEFT JOIN Orders o ON c.ID = o.Cust_ID WHERE o.Status = 'Shipped'
  • Task 3: What is the difference between filtering on a column in the ON clause vs. in the WHERE clause for a LEFT JOIN?