SQL NOT IN and NULL

What & Why

NOT IN (list) is shorthand for a chain of <> comparisons joined by AND. If any single value in that list is NULL, one of those <> comparisons becomes UNKNOWN — and per three-valued logic's AND rules from earlier in this series, UNKNOWN AND anything-not-FALSE stays UNKNOWN. The practical result: the whole WHERE clause evaluates to UNKNOWN or FALSE for every single row, and the query returns nothing at all.

See How It Works

WITH converted_campaign_ids AS (
  SELECT
    CASE WHEN converted_at IS NOT NULL THEN campaign_id END AS campaign_id
  FROM marketing.leads
)
SELECT
  c.id,
  c.name,
  c.id NOT IN (
    SELECT campaign_id FROM converted_campaign_ids
  ) AS unsafe_not_in,
  c.id NOT IN (
    SELECT campaign_id
    FROM converted_campaign_ids
    WHERE campaign_id IS NOT NULL
  ) AS safe_not_in
FROM marketing.campaigns c
ORDER BY c.id;