SQL IN Operator: Subqueries, NOT IN, and the NULL Trap That Returns Zero Rows

Suppose an interviewer asks for every company with no matching recorded company id in the free-subscription table. Four companies exist, and only Contoso has a matching id. You expect three rows: Northwind, Fabrikam, and Adventure.

The obvious NOT IN query returns no rows because the subscription data also contains one NULL company id. This guide walks through that result from input tables to output tables, with a row-flow diagram between each query and its result.

The page is aimed primarily at data scientist interviews, where exclusion metrics, membership filters, duplicate matches, and missing keys often appear together. The same reasoning applies to data analyst and data engineer work.

Start with the input tables

Use these two tables for every example. You do not need to read setup or insert statements; the rows below are the complete starting data.

companies

free_subs

There are two details to notice before writing SQL:

• Contoso appears twice in free_subs.
• One subscription row has an unknown company id.

The duplicate tests whether a query accidentally multiplies companies. The NULL row is an import audit record known not to be attributable to any of the four listed companies. It tests whether an exclusion can distinguish false from unknown without changing the intended answer.

Positive membership with IN

The first operation asks: Which companies appear at least once in free_subs?

Query

SELECT company_id, company_name
FROM companies
WHERE company_id IN (
  SELECT company_id
  FROM free_subs
)
ORDER BY company_id;

Row flow

Output

IN combines equality comparisons with OR. Contoso has a true comparison with company id 2, so it survives. The extra NULL comparison is unknown, but TRUE OR UNKNOWN is still true.

For Northwind, Fabrikam, and Adventure, none of the known values match. Their comparisons include false and unknown, but no true, so the WHERE clause removes them. A WHERE clause keeps only true; both false and unknown are filtered out.

Why NOT IN returns zero rows

Now ask the original question: Which companies do not appear in free_subs?

Query

SELECT company_id, company_name
FROM companies
WHERE company_id NOT IN (
  SELECT company_id
  FROM free_subs
)
ORDER BY company_id;

Row flow

NOT IN must prove that a company id is different from every value returned by the subquery. For Fabrikam, that logic is:

3 NOT IN (2, 2, NULL)
= (3 <> 2) AND (3 <> 2) AND (3 <> NULL)
= TRUE     AND TRUE     AND UNKNOWN
= UNKNOWN

Output

0 rows returned.

Nothing is wrong with the database. This is SQL's three-valued logic. Ordinary = and <> comparisons with NULL return unknown. Predicates designed for missing values, such as IS NULL and IS DISTINCT FROM, have their own defined behavior. Contoso fails because it matches 2; the other three companies fail because the final predicate is unknown rather than true.

The empty-subquery case is different. If the subquery returns no values, there is nothing to disprove, so NOT IN passes every row. Once the subquery returns one NULL, the result changes. That data-dependent switch is why NOT EXISTS is easier to reason about.

The same rule explains why country <> 'US' omits rows whose country is NULL. See SQL not-equal and NULL behavior for that related case.

Fix the exclusion with NOT EXISTS

Keep the same input tables and the same desired output. Change the operation from “prove this id differs from every right-side value” to “keep this company when no matching right-side row exists.”

Query

SELECT c.company_id, c.company_name
FROM companies AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM free_subs AS f
  WHERE f.company_id = c.company_id
)
ORDER BY c.company_id;

Row flow

Output

This is the strongest interview answer because the logic matches the business question directly. The NULL row does not equal any company id, so it never establishes a match. Duplicate rows for Contoso do not change the yes-or-no result.

Two alternatives can return the same three-row output, but they need extra assumptions:

The anti-join is useful when a team prefers join syntax. Review SQL joins and row survival before placing it inside a larger metric query, because joins can change grain. The filtered NOT IN repair is valid here because the input definition says the orphan import row is not attributable to any listed company. A later edit can reintroduce the trap, so NOT EXISTS remains the safer default.

When a JOIN should multiply rows

Membership operators answer whether a related row exists. A join is different: use it when the output needs columns from the related table.

Here the input remains the same, but the output grain changes to one row per subscription grant.

Query

SELECT
  c.company_id,
  c.company_name,
  f.granted_on
FROM companies AS c
JOIN free_subs AS f
  ON f.company_id = c.company_id
ORDER BY f.granted_on;

Row flow

Output

The two rows are correct because the query asks for grant dates. They would be a bug only if the intended grain were one row per company. Adding DISTINCT can hide that mismatch rather than fix it; SQL DISTINCT and fanout explains why.

For performance, start with correct semantics and inspect the actual plan. PostgreSQL can often turn positive membership into a semi-join and NOT EXISTS into an anti-join, but statistics, indexes, memory, row counts, and engine version matter. “EXISTS is always faster” is not a defensible answer. A stronger answer is: “I would choose the form with the right row semantics, then verify with EXPLAIN (ANALYZE, BUFFERS) on representative data.”

Before finishing an interview query, test:

• no matching row
• one matching row
• duplicate matching rows
• a NULL key on the right
• a NULL key on the left, if the outer key is nullable

Practice the full anti-join prompt in SQL practice questions, then use the SQL interview question bank to repeat the same reasoning with a different schema.

FAQ

When is NOT IN safe?

It is safe when the left expression is non-NULL and the list or subquery is guaranteed to contain no NULL values. A database NOT NULL constraint is a stronger guarantee than a comment or an assumption about current data. Even with that guarantee, NOT EXISTS often communicates an exclusion more directly.

Why does IN still find Contoso when the subquery contains NULL?

IN combines comparisons with OR. One true comparison is enough, so Contoso matches company id 2 even though another subquery row is unknown. A non-matching value has false and unknown comparisons but no true comparison, so it does not pass.

Is NOT EXISTS always faster than NOT IN?

No universal timing claim is correct across database engines and datasets. NOT EXISTS is the better default because its NULL semantics match an anti-join and many optimizers can plan it efficiently. Confirm performance with the execution plan and representative data.

Should I use COALESCE to replace the NULL?

Only when the replacement value has a real business meaning and cannot collide with a legitimate key. Replacing NULL with a sentinel such as -1 just to make NOT IN run can create a different correctness bug. A correlated NOT EXISTS avoids the need for a sentinel.

Which roles are most likely to see this question?

Data scientist and data analyst interviews commonly test it through retention, eligibility, or exclusion metrics. Data engineer interviews may add schema constraints and plan analysis. Backend interviews can reach the same issue in reporting or authorization queries, but this guide is primarily targeted to data scientist preparation.