SQL / JOINS
The LEFT JOIN plus WHERE bug that silently drops rows
Recognize why a WHERE filter on the right table turns a LEFT JOIN into an INNER JOIN, and fix it by moving the condition into ON.
What you will learn
- Spot null-rejecting WHERE predicates that quietly turn a LEFT JOIN into an INNER JOIN
- Move right-table filters into ON and leave only NULL-aware tests in WHERE
- Write anti-joins as ON plus WHERE right.pk IS NULL, never as a filtered WHERE
- Verify a LEFT JOIN returns at least one row per row of the preserved table
Understanding The LEFT JOIN plus WHERE bug that silently drops rows
A LEFT JOIN produces two kinds of rows: genuine pairs, and left rows that found no partner and are padded with NULL in every right-hand column. WHERE is evaluated after that padding exists, on the assembled row, so t.week = 36 ends up comparing 36 with NULL. That comparison is not false, it is unknown, and WHERE keeps a row only when the predicate is true, so every padded row is discarded. The LEFT JOIN now returns exactly what an INNER JOIN would return, with no error and no warning.
What matters about a predicate is whether it can ever be true while the right-hand columns are NULL. Predicates that cannot are called null-rejecting, and query planners recognize them and rewrite the outer join as an inner join outright, which is why EXPLAIN sometimes reports a join type you never wrote. Any =, <, LIKE, IN or <> against a right-hand column is null-rejecting, while a predicate that mentions only left-hand columns is not: WHERE e.name LIKE 'A%' after the same join is harmless.
The repair follows from what each clause is for. ON decides which right rows are eligible to pair with a left row; WHERE decides which assembled rows survive, so a per-week restriction is part of the pairing rule and belongs in ON. Notice what that means for Grace below: she has timesheets, just not for week 36, so she comes back with NULL hours rather than her 20 hours from week 35. When you really do need the test after the join, spell out the NULL case with t.week = 36 OR t.week IS NULL, COALESCE(t.hours, 0) = 0, or the deliberate anti-join WHERE t.ts_id IS NULL.
CREATE TABLE employees (
emp_id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE timesheets (
ts_id INTEGER PRIMARY KEY,
emp_id INTEGER NOT NULL,
week INTEGER NOT NULL,
hours INTEGER NOT NULL
);
INSERT INTO employees VALUES (1, 'Ada'), (2, 'Grace'), (3, 'Linus');
INSERT INTO timesheets VALUES
(10, 1, 36, 40),
(11, 1, 35, 38),
(12, 2, 35, 20);
-- Intent: every employee, plus week 36 hours if they logged any.
-- Broken: the filter runs after the join and deletes the padded rows.
SELECT e.name, t.hours
FROM employees e
LEFT JOIN timesheets t ON t.emp_id = e.emp_id
WHERE t.week = 36
ORDER BY e.name;
-- Fixed: the filter now decides which right rows may match.
SELECT e.name, t.hours
FROM employees e
LEFT JOIN timesheets t ON t.emp_id = e.emp_id AND t.week = 36
ORDER BY e.name;A WHERE condition on a right-table column cannot be true for a NULL-padded row, so it deletes precisely the unmatched rows the LEFT JOIN was written to keep.
Worked examples
The one WHERE test that belongs there
Finding employees with no week 36 timesheet, the correct way: filter in ON, test for the padded row in WHERE.
-- uses the employees / timesheets tables above
SELECT e.name
FROM employees e
LEFT JOIN timesheets t
ON t.emp_id = e.emp_id
AND t.week = 36
WHERE t.ts_id IS NULL
ORDER BY e.name;Example explained
Line 1AND t.week = 36 sits in ON, so Grace's week 35 row never counts as a match and cannot hide her from the IS NULL test.
Line 2WHERE t.ts_id IS NULL selects padded rows on purpose; here it means 'no eligible partner was found'.
Line 3Test ts_id, a NOT NULL primary key: had you tested a nullable column such as hours, a real row with a missing value would be misreported as missing entirely.
Line 4Ada is excluded because her week 36 row matched, so her ts_id is 10 rather than NULL.
Negation on the right side is worse
An inequality against a right-table column removes the unmatched rows as well, which is the opposite of what the query means to say.
-- uses the employees / timesheets tables above
-- "employees who did not log 40 hours in week 36"
SELECT e.name, t.hours
FROM employees e
LEFT JOIN timesheets t ON t.emp_id = e.emp_id AND t.week = 36
WHERE t.hours <> 40
ORDER BY e.name;
-- NULL-aware version
SELECT e.name, t.hours
FROM employees e
LEFT JOIN timesheets t ON t.emp_id = e.emp_id AND t.week = 36
WHERE t.hours IS NULL OR t.hours <> 40
ORDER BY e.name;Example explained
Line 1Ada is removed correctly, because 40 <> 40 is false.
Line 2Grace and Linus carry padded NULL hours, and NULL <> 40 is unknown, so the first query returns nothing at all even though both are valid answers.
Line 3t.hours IS NULL OR t.hours <> 40 gives the padded row an explicit branch that evaluates to true.
Line 4Where the dialect supports it, t.hours IS DISTINCT FROM 40 expresses the same NULL-safe test in one term.
A row count that exposes the bug
Comparing the join result against the preserved table's row count turns a silent data loss into a visible number.
-- uses the employees / timesheets tables above
SELECT
(SELECT COUNT(*) FROM employees) AS left_rows,
(SELECT COUNT(*)
FROM employees e
LEFT JOIN timesheets t ON t.emp_id = e.emp_id
WHERE t.week = 36) AS joined_rows;Example explained
Line 1A LEFT JOIN can never return fewer rows than its left table, so joined_rows below left_rows proves something filtered rows after the join.
Line 2The count can legitimately exceed left_rows when one left row matches several right rows, so this check flags dropped rows, not duplicated ones.
Line 3Run it whenever a per-entity report looks suspiciously short; the broken query itself raises no error to investigate.
Important notes
A padded row is a placeholder, not data: after a LEFT JOIN, COUNT(*) counts it as 1 and SUM(t.hours) returns NULL for someone with no matches, so use COUNT(t.ts_id) and COALESCE(SUM(t.hours), 0) when zero is the right answer.
The placement only changes the result for outer joins; with an INNER JOIN, ON and WHERE return the same rows, which is why the habit of dumping every filter into WHERE goes unpunished until the first LEFT JOIN.
Common mistakes
Putting a date, status or category filter on the joined table in WHERE, for example WHERE t.week = 36. Every employee with no matching timesheet vanishes from the report instead of appearing with no hours, and because the surviving rows are all correct nobody notices the missing people.
Excluding a value with WHERE t.status <> 'cancelled' or NOT IN after a LEFT JOIN. Unmatched rows have a NULL status, the comparison is unknown, and rows with no related record at all are thrown out along with the cancelled ones.
Writing the anti-join as WHERE t.hours IS NULL instead of testing a NOT NULL key column. A genuinely matched row that happens to store NULL in hours is then reported as 'no timesheet', inflating the list of offenders.
Try it yourself
Change, predict, then run
Create a students table with four rows and a submissions table in which only two of them have a row for assignment 3, then write the query returning all four students with their assignment 3 score. Move assignment = 3 from ON into WHERE, confirm you drop to two rows, and then rewrite it as an anti-join listing the students who never submitted.
Open the SQL workspaceCheck your understanding
A LEFT JOIN from employees to timesheets is followed by WHERE t.week <> 36. Linus has no timesheet rows at all. Does he appear in the result?
- Yes, because he has no week 36 row, so 'not week 36' is satisfied
- No, because a LEFT JOIN keeps only left rows whose key matches a right row
- No, because his padded row has t.week = NULL, and NULL <> 36 is unknown rather than true
- Yes, because a LEFT JOIN guarantees every left row survives whatever WHERE says
Show answer
WHERE runs on the assembled row, and for Linus every timesheet column is a NULL placeholder; NULL <> 36 evaluates to unknown and WHERE keeps only rows that are true, so he is dropped. The first option is the tempting one because it reads <> 36 as the English 'has no week 36', but SQL is comparing a manufactured NULL, not the absence of a row. The last option fails for the same reason: the LEFT JOIN really does emit Linus, and WHERE then removes him.