Lesson 29 of 61

5 mins read

SQL LEFT JOIN: Preserving Unmatched Left Rows & Finding Orphans

In Plain English

The `LEFT JOIN` (or `LEFT OUTER JOIN`) returns ALL records from the left table, along with matched records from the right table. If there is no match on the right, the result set fills all right-table columns with `NULL`. Adding `WHERE right.id IS NULL` creates an Anti-Join to find orphan records.

The Mental Model

"`LEFT JOIN` is like taking attendance for an entire classroom (Left Table): every student's name appears on the list. If a student turned in homework (Right Table), their score is printed; if not, their score shows as blank (NULL)."

Deep Dive: How It Works

LEFT JOIN vs LEFT OUTER JOIN: The keyword `OUTER` is optional; `LEFT JOIN` and `LEFT OUTER JOIN` are 100% identical.

Finding Inactive / Orphan Rows (Anti-Join): `SELECT c.name FROM customers c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.id IS NULL` finds all customers who have NEVER placed an order.

Filtering Trap in WHERE: Placing a right-table condition in the `WHERE` clause (`WHERE o.status = 'shipped'`) accidentally turns your LEFT JOIN into an INNER JOIN by filtering out NULL rows! Move right-table filters into the `ON` clause.

Syntax Blueprint

SELECT c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON c.id = o.customer_id;

All rows from customers are kept; missing orders are padded with NULL.

Core Rules to Remember

Preserves Left Table: Every single record in the left table is guaranteed to appear in output at least once.

Anti-Join Pattern: Combining `LEFT JOIN` with `WHERE right.key IS NULL` isolates records lacking relationships.

Filter in ON vs WHERE: Filters on the right table belong in the ON clause to preserve left row retention.

Common Beginner Traps & How to Fix Them

Mistake:Adding `WHERE o.created_at >= '2026-01-01'` on a LEFT JOIN table and losing customers with no orders.

Why it happens: WHERE drops rows where o.created_at is NULL.

How to fix: Put condition in ON clause: `LEFT JOIN orders o ON c.id = o.customer_id AND o.created_at >= '2026-01-01'`.

Pro Tip: Use `COALESCE(SUM(o.amount), 0)` with LEFT JOIN to return 0.00 for customers who have zero orders instead of NULL.

Live Interactive Example

Hit Run Code to see it live

Customer Order Activity and Finding Zero-Order Customers

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: LEFT JOIN preserved Bob and Charlie with 0 orders, and Anti-Join isolated them cleanly.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Write a LEFT JOIN Query

Select `u.name`, `p.title` from `users u LEFT JOIN posts p ON u.id = p.user_id` ordered by `u.id`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.