SQL EXISTS vs IN: get membership and NULLs right

The report used to show subscribers who had never clicked. Today it returns no rows. The table still contains inactive subscribers, the SQL still runs, and nobody changed the query. A tracking import added a click whose subscriber could not be identified, so its subscriber ID is NULL.

This is where arguments about SQL EXISTS vs IN often start at the wrong end. Before asking which is faster, establish which rows the query means to return. Positive membership, absence of a match, duplicate handling, and nullable keys are different questions. A rewrite that saves a millisecond while changing the answer is a bug with a pleasant execution plan.

Suppose subscribers 1, 2, and 3 exist. Subscriber 1 clicked twice. One further click has an unknown subscriber. The data is intentionally small enough to reason about without a database console:

CREATE TEMP TABLE subscribers (id integer PRIMARY KEY);
CREATE TEMP TABLE clicks (subscriber_id integer NULL);

INSERT INTO subscribers VALUES (1), (2), (3);
INSERT INTO clicks VALUES (1), (1), (NULL);

To find subscribers with at least one click, either of these predicates expresses membership:

SELECT s.id
FROM subscribers AS s
WHERE EXISTS (
    SELECT 1
    FROM clicks AS c
    WHERE c.subscriber_id = s.id
);

SELECT s.id
FROM subscribers AS s
WHERE s.id IN (SELECT c.subscriber_id FROM clicks AS c);

Both queries return subscriber 1 once. They filter rows from subscribers; the two matching clicks do not create two subscriber rows. An ordinary inner join would create two matches unless a uniqueness constraint or additional operation changed that result. Adding DISTINCT after a join can conceal that mismatch in intent while introducing extra work.

EXISTS asks whether its subquery produces a row. IN compares a value with the subquery's values. The literal 1 in the existence query communicates that no payload is needed. It is not a magic performance setting. PostgreSQL documents these subquery expression semantics, including their treatment of nulls.

The tempting way to find subscribers without clicks is to negate the positive query:

SELECT s.id
FROM subscribers AS s
WHERE s.id NOT IN (SELECT c.subscriber_id FROM clicks AS c);

The result is empty for our data. For subscriber 2, the comparison against 1 is false, but the comparison against the unknown subscriber is unknown. SQL cannot conclude that 2 is absent from the entire set when one member's value is unknown. Negating unknown still produces unknown, and a WHERE clause keeps only true results.

You can expose that difference directly:

SELECT
    2 IN (1, NULL) AS is_member,          -- NULL / unknown
    2 NOT IN (1, NULL) AS is_absent,      -- NULL / unknown
    1 NOT IN (1, NULL) AS known_match;    -- false

The correct expression for our report is the absence of a matching click:

SELECT s.id
FROM subscribers AS s
WHERE NOT EXISTS (
    SELECT 1
    FROM clicks AS c
    WHERE c.subscriber_id = s.id
);

Now subscribers 2 and 3 appear. The unknown click matches neither subscriber under ordinary equality, so it cannot make the inner query produce a row for either one.

Write the relationship you mean, then inspect the work the database chose to do.

Filtering nulls out of the NOT IN subquery also fixes this example. That can be reasonable when the application explicitly defines unknown values as irrelevant. NOT EXISTS usually states the report's business question more directly: there is no related click. The difference is legibility and semantics first, not a universal speed ranking.

There is another boundary worth testing. Our subscriber ID is a primary key and cannot be null. If the outer expression is nullable, its behavior needs a separate decision too. Do not generalize this rewrite to a nullable email address without deciding whether two missing email addresses should count as the same value. A schema constraint is part of the proof that two queries agree.

An existence relationship is often represented as a semi join: retain a left-side row when a matching right-side row exists, without copying all right-side matches into the output. Its absence counterpart is an anti join. These describe logical relationships; they do not tell you whether the engine will use a hash table, an index lookup, or another physical strategy.

SQL Server's join documentation distinguishes logical and physical joins and explains the optimizer's role in choosing algorithms. That distinction is why counting keywords is a poor performance diagnostic. Depending on the engine, constraints, and query shape, equivalent IN and EXISTS queries can reach similar plans. A correlated subquery in the text does not prove that the engine executes a naive full scan once per outer row.

For our example, consider two possible strategies without pretending either is the actual plan. One reads click keys into a lookup structure and checks each subscriber against it. Another scans subscribers and probes an index on clicks.subscriber_id. If the report restricts the outer side to three selected subscribers, repeated index probes could be attractive. If it processes the entire subscriber table, reading the click relation once might be attractive. The row estimates and costs decide; the English preference for EXISTS does not.

An index is therefore a hypothesis to test, not a ritual:

CREATE INDEX clicks_subscriber_id_idx ON clicks (subscriber_id);

EXPLAIN (ANALYZE, BUFFERS)
SELECT s.id
FROM subscribers AS s
WHERE NOT EXISTS (
    SELECT 1
    FROM clicks AS c
    WHERE c.subscriber_id = s.id
);

This PostgreSQL command executes the query and reports observed work. Its EXPLAIN guide describes estimates, actual row counts, loops, and buffer information. Use representative data; three subscribers are excellent for a correctness proof and useless for judging an index's production value.

Build the performance experiment around the report's actual filters. Include a large subscriber with many clicks, subscribers with none, and the nullable import row. Preserve the same projection and outer predicates in both candidates. Otherwise you might be measuring a smaller result rather than a better plan.

Before timing anything, compare the result sets. Then inspect estimated versus actual row counts at the nodes that process clicks. A large error suggests a statistics or data-distribution problem worth understanding before rewriting syntax. Check repeated loops and total rows visited. Look for extra duplicate elimination introduced by a join-based rewrite.

Run more than one representative parameter set. A customer with ten subscribers and a customer with a million can justify different plans. Record cache conditions and repeat the measurement, but do not publish one warm run as a universal result. The query-plan guide walks through the same discipline for application queries.

If both forms produce the same plan and equivalent observed work, choose the expression that most clearly communicates the relationship. That is a useful result. An experiment does not have to discover a winner to remove uncertainty.

The essential regression fixture is the nullable click, because a dataset containing only known foreign keys lets the broken query pass. Also include two clicks for one subscriber so an accidental join cannot quietly duplicate the report. Verify the empty inner table, where every subscriber should qualify as having no clicks, and the all-matched case, where none should.

Avoid making a plan's exact text the only test oracle. A database upgrade or changed statistics can legitimately choose another plan while preserving the contract. Correctness assertions should describe rows. Performance checks should describe the resource budget or structural constraint that matters, using the database and fixtures the application actually supports.

The general lesson extends beyond subscriber reports. Orders without shipments, users without permissions, and devices without a heartbeat are all absence queries. A missing relationship is not the same as a known value failing every comparison in a set that contains unknown values. Getting comfortable with that sentence saves more debugging time than memorizing a slogan about which SQL keyword is fastest.

Katabench's "Subscribers Who Never Clicked" exercise targets this exact null-safe anti-join problem. The track guide explains the database practice format, and how grading works describes the checks behind a submission. Start by predicting the rows for the tiny fixture above, then use the exercise to prove the query handles the hostile case as well as the ordinary one. Read the plan only after the answer is right.