What is LEFT JOIN in SQL? A beginner's guide

LEFT JOIN (full name LEFT OUTER JOIN) is a join where every row from the left table ends up in the result. When there's no match on the right, the right-side columns are NULL.

Contrast with INNER JOIN: that one drops rows without a match. LEFT JOIN keeps them, just marks "nothing on the right".

Analogy. A teacher hands out tests. Not everyone turned theirs in.

• INNER JOIN — "show me only students who turned in a test, with their grade".
• LEFT JOIN — "show me every student; grade if they turned it in, blank if they didn't".

The second is more often what you need in real life — you want to see who's lagging behind.

Why LEFT JOIN matters

Classic scenarios:

1. A list plus optional info, even if it's missing. All users + how many orders they have (including 0).
2. Finding orphans — rows without a match. Users with no orders. Products no one ever bought. Posts with no comments.
3. Counting gaps in the data. How many lessons have no homework, how many posts have no comments.

INNER JOIN won't work here — it drops the "childless" rows on the spot.

Basic syntax

SELECT columns
FROM table_A         -- left
LEFT JOIN table_B    -- right
  ON A.key = B.key;

"Left" is whatever follows FROM. "Right" is whatever follows LEFT JOIN. Remember the direction: LEFT means the left side is fully preserved.

OUTER is optional. LEFT JOIN and LEFT OUTER JOIN are the same thing.

Example: a classroom

students:

id	name
1	Anna
2	Boris
3	Vera
4	Grisha

grades (test grades):

id	student_id	score
10	1	5
11	1	4
12	2	3

Query: "every student with their grades, if any".

SELECT s.name, g.score
FROM students s
LEFT JOIN grades g ON s.id = g.student_id;

Result:

name	score
Anna	5
Anna	4
Boris	3
Vera	NULL
Grisha	NULL

What to notice:

• All four students appear in the result. That's the whole point of LEFT JOIN.
• Anna has two grades — two rows.
• Boris has one — one row.
• Vera and Grisha have no grades → one row each, with NULL in score.

If we'd written INNER JOIN, only Anna (two rows) and Boris would remain. Vera and Grisha would drop — the very people the teacher cares about.

Finding orphans — rows without a match

Classic trick. To find students who haven't taken any test, filter on IS NULL:

SELECT s.name
FROM students s
LEFT JOIN grades g ON s.id = g.student_id
WHERE g.id IS NULL;

Result:

name
Vera
Grisha

Logic: LEFT JOIN keeps every student. The ones with no grades have NULL in g.id. WHERE g.id IS NULL keeps just them.

This pattern (LEFT JOIN ... WHERE other.id IS NULL) is the standard way to find "no match". Used everywhere: users with no orders, posts with no comments, products no one bought.

Don't filter the right table in WHERE

The trap. If you want only A-grades and write:

SELECT s.name, g.score
FROM students s
LEFT JOIN grades g ON s.id = g.student_id
WHERE g.score = 5;

Result:

name	score
Anna	5

Vera and Grisha dropped out. Because for them WHERE g.score = 5 evaluates to NULL = 5 (not TRUE) and they get filtered out. LEFT JOIN effectively became INNER JOIN.

If you want to keep all students but only show the score when it's a 5 — the condition belongs in ON, not in WHERE:

SELECT s.name, g.score
FROM students s
LEFT JOIN grades g ON s.id = g.student_id AND g.score = 5;

Result:

name	score
Anna	5
Boris	NULL
Vera	NULL
Grisha	NULL

Now all four are in the result; the score appears only when it's 5. Subtle but critical difference. Remember: conditions on the right table → ON; on the left → WHERE.

INNER vs LEFT — visualised

Imagine two soccer-team tables: players and goals (goals scored). Not every player scored.

• INNER JOIN → only players who scored at least once.
• LEFT JOIN from players to goals → every player; players with no goals get NULL in goal columns.
• LEFT JOIN + WHERE goals.id IS NULL → only players who didn't score at all.

Three different questions — three different queries.

Bigger example: e-commerce

users:

id	name	signup_date
1	Anna	2024-01-15
2	Boris	2024-02-01
3	Vera	2024-02-20
4	Grisha	2024-03-10

orders:

id	user_id	amount	created_at
50	1	500	2024-02-05
51	1	1500	2024-02-10
52	2	200	2024-02-25

Business ask: "how many orders and total spent per registered user? Include those who haven't bought anything yet".

SELECT
  u.name,
  COUNT(o.id)               AS orders_count,
  COALESCE(SUM(o.amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
ORDER BY total_spent DESC;

Result:

name	orders_count	total_spent
Anna	2	2000
Boris	1	200
Vera	0	0
Grisha	0	0

Key takeaways:

• All 4 users appear, even Vera and Grisha with zero orders.
• COUNT(o.id) returns 0 for them (NULL doesn't count in COUNT).
• SUM would return NULL for them, so we wrap it in COALESCE(..., 0) — turn NULL into 0 for a clean output.

A bread-and-butter dashboard query: "per-user activity, including zero".

Common beginner mistakes

1. WHERE on the right table, accidentally turning LEFT into INNER. The most common and sneakiest mistake. Filters on right-side columns → ON. Filters on left-side columns → WHERE.

2. Getting scared by NULLs in the result. They're not a problem — they're a signal "no match on the right". Use IS NULL, COALESCE, NULL-skipping aggregates, and you're fine.

3. Using LEFT JOIN when INNER is needed. If the business says "only users who HAVE orders" — that's INNER JOIN. LEFT JOIN gives extra rows here and may slow the query down.

4. Stacking multiple LEFT JOINs and losing track. Chains of A LEFT JOIN B LEFT JOIN C are tricky — each new condition is relative to what's already there. For complex joins, build incrementally.

5. RIGHT JOIN instead of swapping tables. RIGHT JOIN exists and does the mirror image. In practice almost no one uses it — just swap the tables and stick with LEFT JOIN. Easier to read.

6. COUNT(*) vs COUNT(column) after LEFT JOIN. COUNT(*) counts every row, including ones where the right side is NULL. COUNT(o.id) counts only real matches. With LEFT JOIN these give different numbers.

Quick recap

• LEFT JOIN keeps EVERY row from the left table.
• If the right side has no match — its columns are NULL.
• LEFT JOIN ... WHERE other.id IS NULL is the standard way to find unmatched rows.
• Conditions on the right side go in ON; on the left side, in WHERE.
• OUTER is optional; LEFT JOIN = LEFT OUTER JOIN.
• More common in real-world tasks than INNER, since you usually want to see the full picture including zeroes.

Try it yourself

On users and orders above:

1. List ALL users with the number of orders they have (including zero).
2. Find users with no orders at all (IS NULL pattern).
3. List ALL users with their most recent order (if any). Hint: LEFT JOIN + MAX(created_at) + GROUP BY.

When LEFT JOIN clicks, a whole class of problems opens up that just can't be solved without it.