Sellers With No Sales

A LEFT JOIN keeps every seller and attaches their 2020 orders; the rows that come back with O.seller_id IS NULL are exactly the sellers for whom no matching 2020 order existed — that is the anti-join, and it only works because the year condition lives in the ON clause where it filters what counts as a match, not in WHERE where it would filter rows after the join has already happened.

The problem

Given Seller(seller_id, seller_name) and Orders(order_id, sale_date, order_cost, customer_id, seller_id), report the names of every seller who made no sale in 2020, ordered by seller_name ascending.

The phrase "made no sale" is the tell: you are looking for absence of a row. There is no column that says "this seller had no orders" — that fact only exists as a gap, and the canonical way to surface a gap in SQL is the LEFT-JOIN / IS NULL anti-join.

The data we will trace

Four sellers; the Orders table has 2019 and 2020 rows. Watch Frank: he sold in 2019 but not 2020.

Daniel, Elizabeth, William all have a 2020 order. Frank's only order is 2019-09-04. Expected answer: Frank.

The query

SELECT S.seller_name
FROM   Seller AS S
       LEFT JOIN Orders AS O
              ON  S.seller_id = O.seller_id
              AND YEAR(O.sale_date) = '2020'
WHERE  O.seller_id IS NULL
ORDER  BY S.seller_name;

Two conditions sit in ON: the join key and the year. WHERE does one thing only — keep the rows that failed to match.

Step-by-step trace

Step 1 — LEFT JOIN with both conditions in ON

For each seller, SQL looks for an Orders row where seller_id matches and the year is 2020. A seller with no such row still survives, padded with NULLs (that is what LEFT JOIN guarantees).

Frank's 2019 order did not satisfy YEAR(O.sale_date)='2020', so it was never a match — Frank is kept as a NULL-padded row. That NULL is the signal we are about to read.

Step 2 — WHERE O.seller_id IS NULL

Keep only rows where the right side came back empty:

Step 3 — SELECT + ORDER BY

Project seller_name, sort ascending. One row remains:

Why the naive version is wrong

The tempting "clean up" is to move the year into WHERE:

-- BROKEN: silently behaves like an INNER JOIN
SELECT S.seller_name
FROM   Seller AS S
       LEFT JOIN Orders AS O ON S.seller_id = O.seller_id
WHERE  YEAR(O.sale_date) = '2020'   -- ✖ wrong place
   AND O.seller_id IS NULL;          -- ✖ can never be satisfied

Trace it against Frank. The ON now matches only on id, so Frank joins to his real 2019 row — O.sale_date = 2019-09-04, not NULL. Then WHERE runs:

• YEAR(O.sale_date)='2020' is YEAR('2019-09-04')='2020' → false, so Frank's row is dropped — he never reaches the answer.
• For any seller who genuinely had no orders at all, the LEFT JOIN pads with NULL, and YEAR(NULL)='2020' evaluates to NULL (unknown), which is also not true → dropped.

So the two WHERE conditions are mutually exclusive: a row can't simultaneously have YEAR(sale_date)='2020' (needs a non-NULL date) and O.seller_id IS NULL. The query returns empty. The fix is mechanical: filters on the outer (right) table of a LEFT JOIN belong in ON, because ON decides what counts as a match before padding happens; only the IS NULL probe belongs in WHERE.

Pitfalls

• Year filter in WHERE. The headline bug above: moving any predicate on the right table to WHERE collapses the LEFT JOIN into an INNER JOIN and returns nothing (or wrong rows). Rule of thumb: right-table filters → ON; left-table filters → WHERE; the NULL probe → WHERE.
• Probing a NULLable join key. WHERE O.seller_id IS NULL works because seller_id is the join column and is non-null in real Orders rows, so NULL there can only mean "no match." If you instead test O.order_cost IS NULL and a real order legitimately had a NULL cost, you'd misclassify a matched seller as unmatched. Always probe the join key (or a NOT-NULL column).
• YEAR(sale_date) kills index usage. Wrapping the column in a function makes the predicate non-sargable — the planner can't use an index on sale_date. On large tables prefer a half-open range: O.sale_date >= '2020-01-01' AND O.sale_date < '2021-01-01', still inside the ON clause.
• NOT IN with NULLs. A common alternative — WHERE seller_id NOT IN (SELECT seller_id FROM Orders WHERE YEAR(sale_date)='2020') — returns zero rows the moment the subquery yields a single NULL, because x NOT IN (..., NULL) is never true. NOT EXISTS or this anti-join are NULL-safe; NOT IN is a trap.

Takeaways

• "Find rows that have no match" = LEFT JOIN + IS NULL on the join key. The NULL is the absence, made visible.
• The placement rule is the whole lesson: filters on the LEFT-JOINed (right) table go in ON so they shape what matches; WHERE runs after padding and treats every NULL as not-true, which would erase the unmatched rows you came for.
• Always probe a non-nullable column (the join key) for IS NULL, or you'll misclassify matched rows.
• Prefer a date range over YEAR(col) so the predicate stays sargable; prefer NOT EXISTS/anti-join over NOT IN to stay NULL-safe.

Based on LeetCode 1607 "Sellers With No Sales." Join semantics (ON vs WHERE for outer joins, NULL-padding) follow the SQL standard as documented in the PostgreSQL manual ("Joined Tables") and Markus Winand's SQL Performance Explained / use-the-index-luke.com on sargability. NULL-comparison and three-valued-logic behavior per the SQL:2016 standard. Re-authored and deepened for this guide to make the ON-vs-WHERE anti-join mechanism explicit, with a corrected broken-variant walkthrough and a side-by-side query-plan diagram.