SQL LEFT JOIN: WHERE vs ON, Missing Rows, and COUNT

Start with a result contract and a tiny dataset

The requirement is: show every learner and their passed attempts, including learners with none. Ada has two passed attempts, Boris has one failed attempt, and Chen has no attempt. These three cases distinguish a filtered-out match from no match at all.

Run the setup and subsequent queries in the same PostgreSQL session because the tables are temporary. The primary keys are non-null. Before writing a query, decide whether the output should contain one row per attempt or one row per learner; those are different result shapes.

CREATE TEMP TABLE learners (
  id integer PRIMARY KEY,
  name text NOT NULL
);
CREATE TEMP TABLE attempts (
  id integer PRIMARY KEY,
  learner_id integer NOT NULL REFERENCES learners(id),
  status text NOT NULL
);
INSERT INTO learners VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Chen');
INSERT INTO attempts VALUES
  (10, 1, 'passed'), (11, 1, 'passed'), (12, 2, 'failed');

Why the WHERE filter removes learners

This query returns only Ada's attempts 10 and 11. Boris has a joined row, but its status is failed. Chen receives a null-extended right side, and NULL = 'passed' is unknown. WHERE retains only true conditions, so neither learner survives.

For this particular right-side predicate, the result has the same rows as an inner join with that filter. That does not mean every WHERE clause converts a LEFT JOIN to an inner join: a condition on the left table, or a condition accepting nulls, can have different behavior.

SELECT l.name, a.id AS attempt_id
FROM learners l
LEFT JOIN attempts a ON a.learner_id = l.id
WHERE a.status = 'passed'
ORDER BY l.id, a.id;

• PostgreSQL: joins and table expressions

Move the condition into ON to preserve every learner

Here only passed attempts qualify as matches. Boris's failed attempt is excluded during matching, so he receives a null-extended row just like Chen. Ada still has two qualifying matches; a LEFT JOIN does not promise one row per left-hand entity.

Adding OR a.id IS NULL to the earlier WHERE condition is not equivalent. It restores Chen, who had no joined attempt, but not Boris, whose failed attempt already matched the unfiltered join. This counterexample is a useful way to test a proposed repair.

SELECT l.name, a.id AS attempt_id
FROM learners l
LEFT JOIN attempts a
  ON a.learner_id = l.id AND a.status = 'passed'
ORDER BY l.id, a.id;

Count actual matches, not the preserved output row

If the requirement is one row per learner, aggregate at that grain. This query returns Ada 2, Boris 0, Chen 0. COUNT(a.id) skips the null-extended value. COUNT(*) would count the preserved row and incorrectly show 1 for Boris and Chen.

The choice of a.id is deliberate: it is a non-null primary key for every real attempt. Counting a nullable business field could undercount genuine matches. If you later join another one-to-many table, inspect the new row multiplication before assuming the count still represents attempts.

SELECT l.name, COUNT(a.id) AS passed_attempts
FROM learners l
LEFT JOIN attempts a
  ON a.learner_id = l.id AND a.status = 'passed'
GROUP BY l.id, l.name
ORDER BY l.id;

• PostgreSQL: aggregate functions and COUNT

Find learners without a passed attempt using NOT EXISTS

When you need only learners with no qualifying match, NOT EXISTS expresses the question directly. This query returns Boris and Chen once each; it does not construct all matching attempt rows just to discard them. Its meaning remains clear if Ada gains more passed attempts.

Avoid claiming that this form is always faster than a left anti-join. The actual plan depends on indexes, statistics, and the dataset. Establish semantic equivalence first, then compare plans with the same parameters and realistic data.

SELECT l.name
FROM learners l
WHERE NOT EXISTS (
  SELECT 1 FROM attempts a
  WHERE a.learner_id = l.id AND a.status = 'passed'
)
ORDER BY l.id;

• PostgreSQL: EXISTS subqueries

Check the query before your next SQL interview

Use three test cases whenever you change an outer join: no right-hand row, only ineligible right-hand rows, and multiple eligible rows. Write the expected result before executing the query. A single happy-path match cannot reveal either the missing-row bug or the aggregation bug.

Then explain the query in this order: required output grain, matching condition, row filter, aggregation. Use the SQL question set to practise the broader concepts and the window-function guide when the next requirement becomes ranking attempts within each learner.

• SQL queries and modelling interview questions
• Practise SQL window functions in PostgreSQL

Quick answers

Frequently asked questions