lesson 30 Dates, strings & subqueries · beginner

NOT IN and the NULL trap

Avoid unexpected missing results when NOT IN encounters NULL.

the idea

NOT reverses true and false, but unknown stays unknown. If a NOT IN list contains NULL, a nonmatching comparison can become unknown and disappear from WHERE. Prefer NOT EXISTS for nullable relationships.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

SELECT id
FROM viewers
WHERE id NOT IN (
  SELECT referred_by
  FROM viewers
  WHERE referred_by IS NOT NULL
);

Watch for: Filtering NULL out of the subquery or using NOT EXISTS avoids the unknown-value trap.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Inspect nullable relationships. NULL here means no referring viewer.

   step-1.sql Run

   sql›

   SELECT id,referred_by
   FROM viewers;

2. 2

   See the NOT IN trap. A NULL in this subquery makes NOT IN unknown for nonmatches. Observe the empty result.

   step-2.sql Run

   sql›

   SELECT id
   FROM viewers
   WHERE id NOT IN(
     SELECT referred_by
     FROM viewers
   );

3. 3

   Use an existence test. NOT EXISTS asks whether a matching referring relationship is absent.

   step-3.sql Run

   sql›

   SELECT v.id
   FROM viewers v
   WHERE NOT EXISTS(
     SELECT 1
     FROM viewers r
     WHERE r.referred_by=v.id
   );