LEFT JOIN vs INNER JOIN

1. Why this happens

A LEFT JOIN exists for exactly one reason: to keep every row from the left table, whether or not it has a match on the right. The moment a WHERE clause tests a column that comes from the right-hand table, that guarantee can quietly disappear — the query still runs, still returns rows, and still looks like a LEFT JOIN. It just isn't behaving like one anymore.

2. The dataset

Verified by actually running this against SQLite — not hand-computed. Seven employees, four projects. Two employees — Sneha Kapoor and Divya Pillai — aren't assigned to any project at all, so their project_id is NULL. The rest are spread across an active project, a completed one, and one that's on hold. None of this is a contrived edge case; every row here is completely ordinary data.

CREATE TABLE projects(
  project_id INTEGER,
  project_name TEXT,
  status TEXT
);
CREATE TABLE employees(
  employee_id INTEGER,
  employee_name TEXT,
  department TEXT,
  project_id INTEGER
);

INSERT INTO projects VALUES
 (1,'Website Redesign','active'),
 (2,'Mobile App','active'),
 (3,'Data Migration','completed'),
 (4,'Legacy Cleanup','on_hold');

INSERT INTO employees VALUES
 (1,'Aditi Rao','Engineering',1),
 (2,'Rahul Verma','Engineering',2),
 (3,'Sneha Kapoor','Engineering',NULL),
 (4,'Vikram Singh','Design',1),
 (5,'Meera Nair','Design',3),
 (6,'Arjun Mehta','Data',4),
 (7,'Divya Pillai','Data',NULL);

3. The correct LEFT JOIN

SELECT e.employee_name, p.project_name, p.status
FROM employees e
LEFT JOIN projects p ON p.project_id = e.project_id
ORDER BY e.employee_name;

Every employee shows up. The two unassigned employees get NULL for project_name and status, exactly as a LEFT JOIN is supposed to behave. This is the baseline the rest of this article is about accidentally breaking.

4. The bug

Suppose the actual requirement is "show every employee, along with their project — but only mention the project if it's still active." Reaching for a WHERE clause to filter on status looks like the obvious way to write that:

SELECT e.employee_name, p.project_name, p.status
FROM employees e
LEFT JOIN projects p ON p.project_id = e.project_id
WHERE p.status = 'active'
ORDER BY e.employee_name;

Seven employees became three. Every employee with no project, and every employee on a completed or on-hold project, is gone — not marked, not flagged, just absent, as if the LEFT JOIN had been an INNER JOIN the whole time. Nothing in the query threw an error. Depending on what this feeds, it can look like the company suddenly has three employees.

5. Why it happens

A LEFT JOIN fills in NULL for every right-table column when a row has no match. WHERE p.status = 'active' then runs against that NULL — and in SQL, comparing anything to NULL, including with =, doesn't return TRUE or FALSE. It returns UNKNOWN, a third value equal to neither. A WHERE clause only keeps rows that evaluate to TRUE; UNKNOWN is discarded exactly like FALSE. Every row the LEFT JOIN worked to preserve — by filling its missing side with NULL — gets thrown out by the very next line of the query, because NULL = 'active' can never be TRUE.

6. Fix one: move the condition into ON

SELECT e.employee_name, p.project_name, p.status
FROM employees e
LEFT JOIN projects p
  ON p.project_id = e.project_id AND p.status = 'active'
ORDER BY e.employee_name;

Moving the condition from WHERE into ON changes when it's evaluated. WHERE filters the joined result after the join has already happened. ON is part of how the join decides what counts as a match in the first place — so p.status = 'active' is now a condition on which projects are eligible to match, not a filter on the final output. An employee whose project isn't active simply fails to match anything, which is exactly what a LEFT JOIN is built to handle: no match becomes NULL, not a missing row. All seven employees are back, each showing their active project if they have one, and NULL if they don't.

7. Fix two — and why it isn't the same fix

SELECT e.employee_name, p.project_name, p.status
FROM employees e
LEFT JOIN projects p ON p.project_id = e.project_id
WHERE p.status = 'active' OR p.status IS NULL
ORDER BY e.employee_name;

Adding OR p.status IS NULL also brings back the unassigned employees — but not everyone. Meera Nair and Arjun Mehta, both on real, matched projects that just happen to not be active, are still missing. This version keeps rows where the project column is genuinely NULL (no match at all) or where the status is active, but it still excludes anyone whose project matched and turned out to be completed or on hold.

That's not a bug — it might even be the intended question. But it's a different question than fix one answers. Fix one says: show every employee, and tell me about their project only if it's active. Fix two says: show me employees who are either unassigned or actively working on something, and leave out anyone stuck on an inactive project. Both are valid business questions. They aren't interchangeable, and choosing between them is a decision about what the report is for, not a syntax detail.

8. Which one to use

If the requirement is genuinely "list every row from the left table, full stop" — a driver roster, a customer list, an org chart — any filter on the right table's columns belongs in the ON clause, not WHERE. That keeps the LEFT JOIN's guarantee intact no matter what the filter condition finds.

If the requirement is actually a filter on the combined result — "employees who are either free or working on something active," say — then WHERE is the right clause, but every condition on a nullable right-side column needs an explicit OR ... IS NULL alongside it, or the LEFT JOIN silently degrades into an INNER JOIN the moment that column happens to be NULL.

9. Cheat sheet

10. Try it yourself

The failure mode here never announces itself. The query is syntactically a LEFT JOIN, runs without error, and returns a perfectly reasonable-looking table — it's just the wrong table, missing exactly the rows the LEFT JOIN was written to keep. The fix costs nothing once you know to look for it: any WHERE condition on a right-hand column in a LEFT JOIN is worth a second look before it ships.

Practice JOINs live on TableNotFound →