15. Outer Joins: LEFT, RIGHT & FULL JOIN

Core Idea: Outer joins retain unmatched rows from one or both tables. When no match exists on the opposite side of the join condition, NULL values fill the missing columns.

LEFT OUTER JOIN

Returns all records from the left table and matched records from the right table. Non-matching rows from the right table appear as NULL.

            SELECT c.CustomerName, o.OrderID, o.OrderDate
            FROM Customers AS c
            LEFT JOIN Orders AS o ON c.CustomerID = o.CustomerID;
        

Finding Missing / Unmatched Records

Filter for NULL foreign keys to identify orphaned entities (e.g., customers who haven't placed an order):

            SELECT c.CustomerID, c.CustomerName
            FROM Customers AS c
            LEFT JOIN Orders AS o ON c.CustomerID = o.CustomerID
            WHERE o.OrderID IS NULL;
        

FULL OUTER JOIN & CROSS JOIN

FULL JOIN retains all rows from both tables, filling with NULL wherever there is no match. CROSS JOIN produces a Cartesian product multiplying every row of table A by every row of table B.

            SELECT e.Name, d.DepartmentName
            FROM Employees AS e
            FULL OUTER JOIN Departments AS d ON e.DepartmentID = d.DepartmentID;
        

Advanced Deep Dive ADVANCED

WHERE Clause Traps on Outer Joins: Placing a filter on the right table inside the WHERE clause (e.g., WHERE o.Status = 'Shipped') implicitly converts a LEFT JOIN into an INNER JOIN because NULL rows are discarded. Move the condition into the ON clause to preserve the outer join.

RIGHT JOIN Preference: In practice, developers almost exclusively use LEFT JOIN for readability by arranging the primary driver table on the left.

Performance Considerations: Outer joins force specific join orders in query plans, preventing certain reordering optimizations unless the optimizer can prove empty set conditions.