Part 63: LEFT (OUTER) JOIN: Preserving Unmatched Left Rows

A LEFT JOIN returns all records from the left table, and the matched records from the right table. If there is no match, the right side returns NULL.

1. Syntax

-- Find ALL customers, including those who have never placed an order:
SELECT 
  c.id,
  c.name,
  o.id AS order_id,
  o.total_amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

2. The "Anti-Join" Pattern (Finding Orphans / Inactive Users)

Combine a LEFT JOIN with WHERE right_table.id IS NULL to find records in Table A that have no matching records in Table B:

-- Find customers who have NEVER placed an order:
SELECT c.id, c.name, c.email
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

Key Takeaways

• Use LEFT JOIN when child records are optional.
• Use WHERE right.id IS NULL to find orphan or inactive parent records.

In Part 64, we explore RIGHT (OUTER) JOIN & Why Left Join is Preferred!