Why does a WHERE filter remove rows after a LEFT JOIN?

A LEFT JOIN preserves unmatched left-hand rows, but a later WHERE condition can remove them. To keep every campaign while counting only accepted events, put the event-status condition in ON and count the event ID.

The distinction is documented in PostgreSQL’s table-expression reference: ON controls matches; WHERE filters the joined result. A comparison against a null-extended right-hand value does not evaluate to true.

Define the report before writing the query

This original synthetic example represents a marketing report for US, UK, and German campaigns. The required output is one row per registered campaign, including campaigns with zero accepted events. Events are records, not unique people or verified sales. No traffic or conversion performance is implied.

Create three cases you must retain

Campaign 1 has one accepted and one rejected event. Campaign 2 has only a rejected event. Campaign 3 has no events. The second case exposes a bug that a simple unmatched-row test misses.

CREATE TABLE campaigns (campaign_id INTEGER PRIMARY KEY, market TEXT);
CREATE TABLE events (event_id INTEGER PRIMARY KEY, campaign_id INTEGER, status TEXT);
INSERT INTO campaigns VALUES (1,'US'),(2,'UK'),(3,'DE');
INSERT INTO events VALUES (101,1,'accepted'),(102,1,'rejected'),(103,2,'rejected');

The query that silently loses campaigns

SELECT c.campaign_id, c.market,
       COUNT(e.event_id) AS accepted_events
FROM campaigns AS c
LEFT JOIN events AS e
  ON e.campaign_id = c.campaign_id
WHERE e.status = 'accepted'
GROUP BY c.campaign_id, c.market
ORDER BY c.campaign_id;

Result: only 1 | US | 1. Campaign 2’s rejected event fails WHERE. Campaign 3’s null-extended event status also fails it. This query is appropriate only when the report intentionally excludes campaigns without accepted events.

Put the event condition in ON

SELECT c.campaign_id, c.market,
       COUNT(e.event_id) AS accepted_events
FROM campaigns AS c
LEFT JOIN events AS e
  ON e.campaign_id = c.campaign_id
 AND e.status = 'accepted'
GROUP BY c.campaign_id, c.market
ORDER BY c.campaign_id;

campaign_id | market | accepted_events
1           | US     | 1
2           | UK     | 0
3           | DE     | 0

Now only accepted events qualify as matches. A campaign without any qualifying match still gets a preserved row. Counting the non-null event ID returns zero for that row. The output has all three registered campaigns, with one accepted event in total.

Why OR event_id IS NULL is not a reliable repair

SELECT c.campaign_id, c.market,
       COUNT(e.event_id) AS accepted_events
FROM campaigns AS c
LEFT JOIN events AS e
  ON e.campaign_id = c.campaign_id
WHERE e.status = 'accepted' OR e.event_id IS NULL
GROUP BY c.campaign_id, c.market
ORDER BY c.campaign_id;

This returns campaigns 1 and 3, but still loses campaign 2. Campaign 2 already matched a rejected event on its ID, so the join did not create an unmatched row for it. That event then fails both parts of WHERE. Test a campaign with only nonqualifying events—not just a campaign with no events.

Why COUNT(*) produces false activity

Replace COUNT(e.event_id) in the corrected query with COUNT(*) and all three counts become 1. COUNT(*) counts the preserved output row even when there is no matching event. Count a required right-hand identifier for an event count. If that identifier can be null in real data, first fix or explicitly handle that data-quality issue.

Which filters belong in WHERE?

A filter that intentionally defines the campaign population, such as WHERE c.market IN ('US','UK'), can remain in WHERE. A right-hand date or status condition that defines which events to count belongs in ON when zero-event campaigns must remain. Use the same explicit reporting window across all markets; convert timestamps to the chosen reporting timezone before deciding calendar boundaries.

Joining another one-to-many table can multiply events and inflate counts. Check join grain before adding spend, sessions, or contacts. Do not hide duplication with DISTINCT until you have established what one event means.

Checks before this reaches a dashboard

• Compare output campaign IDs with the intended registry: this fixture needs exactly 1, 2, and 3.
• Include accepted, rejected-only, and no-event campaigns in the test data.
• Reconcile the total accepted-event count to the source under the same filters.
• Keep zero separate from an incomplete or delayed event feed.

Validation: all four behaviors shown here were executed with Python’s SQLite engine: the corrected result, the WHERE result, the failed OR NULL repair, and the COUNT(*) result. PostgreSQL documentation supports the join/filter distinction; this example was not executed against a hosted PostgreSQL database.

Continue practicing

For missing amounts rather than missing matches, read SQL NULL vs Zero. For practical reporting exercises, visit business case studies. Explore more worked examples in the analytics blog.