LEFT JOIN

Keep all left rows — even when there's no match on the right

Intermediate 14 minLEFT JOINOUTER JOIN

Keeping All Left Rows

LEFT JOIN returns all rows from the left table, plus matched rows from the right. When there's no match, the right-side columns fill with NULL instead of dropping the row entirely. Here's what makes it different:

Preserves unmatched rows — INNER JOIN silently drops rows without matches on both sides. LEFT JOIN keeps every left row intact — essential for finding missing data like customers without orders or products without reviews.

The anti-join pattern — LEFT JOIN + WHERE right_table.id IS NULL finds rows that exist on the left but have no match on the right. This powerful pattern answers the question "what's missing?" in a single query.

Table order matters — FROM customers LEFT JOIN orders keeps all customers in the result. FROM orders LEFT JOIN customers keeps all orders instead. Choose which table goes on the left based on what you want to preserve.

Knowledge Canvas

How LEFT JOIN Works

Keep all rows from the left table

ALL left rows survive — even without a match
Unmatched right columns fill with NULL
Table order matters: left table = preserved table
LEFT OUTER JOIN = LEFT JOIN (OUTER is optional)

The Anti-Join Pattern

Find what's missing

LEFT JOIN + WHERE right.id IS NULL = find unmatched rows
Customers with no orders: LEFT JOIN orders ON ... WHERE orders.id IS NULL
Products without reviews, users without logins, etc.
Cleaner alternative: NOT EXISTS (but anti-join is more common)

SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

NULL in LEFT JOIN

Understanding NULL-filled columns

Unmatched rows get NULL in ALL right-table columns
COUNT(right.col) correctly returns 0 for unmatched rows
SUM(right.col) returns NULL for unmatched — wrap in COALESCE
WHERE on right columns filters AFTER join — may eliminate unmatched rows

LEFT JOIN Mistakes

Traps that turn LEFT JOIN into INNER JOIN

WHERE right_table.col = value → eliminates NULLs → becomes INNER JOIN
Move right-table filters to the ON clause instead
Forgetting table order: FROM orders LEFT JOIN customers ≠ FROM customers LEFT JOIN orders
Multiple LEFT JOINs can create unexpected row multiplication

LEFT vs INNER JOIN

Key behavioral differences

LEFT JOIN: preserves all left rows

INNER JOIN: drops unmatched rows

Unmatched → NULL columns

Unmatched → row disappears

Result count ≥ left table rows

Result count ≤ smaller table

Use for: inclusive reports, find gaps

Use for: linked data, matched records

Syntax

Syntax Template

1-- Standard LEFT JOIN — keep all left rows

2SELECT

3 a.col,

4 b.col

5FROM table_a a

6LEFT JOIN table_b b

7 ON a.key = b.key;

9-- Anti-join pattern — find unmatched left rows

10SELECT

11 a.*

12FROM table_a a

13LEFT JOIN table_b b

14 ON a.key = b.key

15WHERE b.key IS NULL;

LEFT JOINAll left rows + matched right rows (or NULL)

Anti-joinLEFT JOIN + WHERE right.id IS NULL = find unmatched rows

Sample Data

customers

12 rows

id	name
C1001	Aarav Sharma
C1002	Sara Chen
C1003	James Wilson
C1004	Maria Garcia
C1005	Yuki Tanaka
C1006	Priya Patel
C1007	Alex Johnson
C1008	Chen Wei
C1009	Emma Brown
C1010	Omar Hassan
C1011	Lena Muller
C1012	Ravi Kumar

orders

12 rows

id	customer_id	total
5001	C1001	59.98
5002	C1002	449
5003	C1001	12.5
5004	C1003	89.99
5005	C1006	245
5006	C1005	159
5007	C1004	45.5
5008	C1009	79
5009	C1012	425
5010	C1007	18.99
5011	C1008	134
5012	C1010	65.5

products

10 rows

id	name	category	price	stock
P101	Wireless Mouse	Electronics	24.99	150
P102	Office Desk	Furniture	189	25
P103	LED Desk Lamp	Furniture	45.5	80
P104	USB-C Cable	Electronics	12.99	500
P105	Bluetooth Speaker	Electronics	79	60
P106	Office Chair	Furniture	95	15
P107	Coffee Maker	Appliances	89.99	40
P108	Notebook Set	Stationery	8.5	200
P109	Wireless Headphones	Electronics	159	30
P110	Standing Desk	Furniture	425	8

reviews

12 rows

id	product_id	customer_id	rating	comment
7001	P101	C1001	5	Excellent mouse, very comfy
7002	P101	C1003	4	NULL
7003	P101	C1005	3	Good but battery drains fast
7004	P102	C1002	5	Solid build, worth every rupee
7005	P102	C1006	4	Spacious — fits two monitors
7006	P103	C1004	4	NULL
7007	P104	C1007	5	Just works, durable braided cable
7008	P105	C1009	5	Great sound for the price
7009	P105	C1012	4	Battery could be better
7010	P107	C1008	3	NULL
7011	P108	C1011	5	Good notebooks, smooth paper
7012	P109	C1001	5	Premium feel, noise cancellation is real

Worked Example

Show all customers and their order count, including those with zero orders.

SQL

1SELECT

2 c.name,

3 COUNT(o.id) AS order_count

4FROM customers c

5LEFT JOIN orders o

6 ON c.id = o.customer_id

7GROUP BY

8 c.id,

9 c.name

10ORDER BY

11 order_count DESC;

LEFT JOIN keeps every customer. Those without orders get NULL for all order columns. COUNT(o.id) counts non-NULL values, so customers with no orders correctly show 0.

Output

5 rows

name	order_count
Aarav Sharma	2
Sara Chen	1
James Wilson	1
Maria Garcia	1
Yuki Tanaka	1

Key Concepts

1All left rows survive — unmatched right columns fill with NULL

2LEFT JOIN + WHERE right.id IS NULL = find what's missing (anti-join)

3Table order matters — the left table is the one being preserved

4INNER JOIN drops unmatched rows silently — LEFT JOIN shows them

Pro Tip

INNER JOIN silently drops unmatched rows. LEFT JOIN preserves them — essential when you need to see what's missing or incomplete.

When to Use

Customers with no orders, products without reviews, gaps in data coverage, inclusive reports showing all items even without activity.

Challenge

Solve the problem below

Find products that have NEVER been reviewed. Show just the product name. (Hint: LEFT JOIN + WHERE IS NULL)

Your Query

INNER JOIN

RIGHT JOIN