SQL OUTER JOINS (Left, Right, Full) 🌓¶

Mentor's Note: INNER JOIN is strict; if you don't have a partner, you aren't invited. OUTER JOIN is inclusive; it says "Come anyway, even if you are alone." It's essential for finding missing data, like "Which customers have never placed an order?" 💡

🌟 The Scenario: The Class Attendance 🎒¶

Table A (Students): The official class roll.
Table B (Attendance): The list of students who signed in today.
INNER JOIN: Only students present today.
LEFT JOIN: All students (Marking "Absent" for those not in Table B). ✅

💻 1. LEFT JOIN (The "All From Here" Join) 👈¶

Returns all rows from the Left table, and the matched rows from the Right table. If no match, the result is NULL.

-- Scenario: List ALL customers, and their orders (if any)
SELECT C.name, O.order_date
FROM customers C
LEFT JOIN orders O ON C.id = O.customer_id;

Result: - Arjun (Ordered Yesterday) - Priya (NULL - Never Ordered)

💻 2. RIGHT JOIN (The "All From There" Join) 👉¶

Returns all rows from the Right table. (Rarely used; usually we just swap the tables and use LEFT JOIN).

-- Scenario: List ALL orders, even if the customer was deleted
SELECT C.name, O.order_date
FROM customers C
RIGHT JOIN orders O ON C.id = O.customer_id;

💻 3. FULL OUTER JOIN (The "Everyone" Join) 🌕¶

Returns records when there is a match in either left or right table.

-- Scenario: Match Employees to Projects
-- Show Emp without Project AND Projects without Emp
SELECT E.name, P.project_title
FROM employees E
FULL OUTER JOIN projects P ON E.project_id = P.id;

🎨 Visual Logic: The Missing Pieces¶

graph LR
    subgraph LJ ["LEFT JOIN"]
    L1((Table A)) --- I1((Intersection))
    R1((Table B)) --- I1
    style L1 fill:#ffbc3b,stroke:#1a1a37,stroke-width:2px
    style I1 fill:#ffbc3b,stroke:#1a1a37,stroke-width:2px
    style R1 fill:rgba(26, 26, 55, 0.1),stroke:#1a1a37
    end

    subgraph RJ ["RIGHT JOIN"]
    L2((Table A)) --- I2((Intersection))
    R2((Table B)) --- I2
    style R2 fill:#ffbc3b,stroke:#1a1a37,stroke-width:2px
    style I2 fill:#ffbc3b,stroke:#1a1a37,stroke-width:2px
    style L2 fill:rgba(26, 26, 55, 0.1),stroke:#1a1a37
    end

    subgraph FJ ["FULL JOIN"]
    L3((Table A)) --- I3((Intersection))
    R3((Table B)) --- I3
    style L3 fill:#ffbc3b,stroke:#1a1a37,stroke-width:2px
    style I3 fill:#ffbc3b,stroke:#1a1a37,stroke-width:2px
    style R3 fill:#ffbc3b,stroke:#1a1a37,stroke-width:2px
    end

The highlighted areas show which data is kept. Matched data is joined, while unmatched data is filled with NULL.

💡 Pro Tip: Finding "The Unmatched"¶

The most common use of a LEFT JOIN is to find missing links.

-- "Who hasn't ordered?"
SELECT name 
FROM customers C
LEFT JOIN orders O ON C.id = O.customer_id
WHERE O.id IS NULL; -- 🔍 The magic filter