NULLs and Three-Valued Logic

Why this lesson matters more than it looks

NULL is not a value: it's the absence of a value, SQL's way of saying "unknown." Almost every silent data bug a DE chases ("why did 4,000 rows vanish from the mart?") traces back to a mishandled NULL. Source systems are full of them: an email that was never collected, a region the app forgot to set, a total that hasn't been computed yet. Learning to reason about NULL correctly is the difference between a pipeline you trust and one that quietly loses data.

Three-valued logic

In most languages a comparison is true or false. In SQL there's a third result: unknown. Any comparison to NULL yields unknown:

5 = NULL        -> unknown   (not false!)
5 <> NULL       -> unknown
NULL = NULL     -> unknown   (two unknowns aren't "equal")

And WHERE only keeps rows where the condition is true. It discards both false and unknown. That's the trap: WHERE email = NULL matches nothing, because = NULL is never true. To test for NULL you must use the special operators IS NULL and IS NOT NULL, which return real true/false:

SELECT customer_id, email
FROM customers
WHERE email IS NULL;      -- correct: finds the missing emails

How NULL poisons NOT IN

Remember the trap from the last lesson. If any value in a NOT IN list is NULL, the whole predicate can collapse to unknown for every row and return nothing:

-- If any customer_id in flagged is NULL, this returns NO rows:
WHERE customer_id NOT IN (SELECT customer_id FROM flagged);

Which side the NULL is on decides everything, and it is the side people get backwards:

• A NULL in the list (flagged) makes customer_id <> NULL unknown for every row, so the whole AND chain is unknown and you get nothing back.
• A NULL in the column being checked (orders.customer_id) only makes that one row's test unknown, so you lose that row and no others.

The fix: filter NULLs out of the subquery, or use NOT EXISTS (Level 2).

COALESCE: supply a default

COALESCE(a, b, c) returns the first non-NULL argument. It's how you replace a missing value with a display default without dropping the row:

SELECT
  customer_id,
  COALESCE(email, 'unknown@example.com') AS email_display,
  COALESCE(region, 'UNSPECIFIED')        AS region_display
FROM customers;

Anatomy.

email IS NULL                     -- true when email is missing
email IS NOT NULL                 -- true when email is present
COALESCE(region, 'UNSPECIFIED')   -- region if present, else the fallback
       └─ tried first ┘  └─ used only when the first is NULL ┘

Keep it readable / the audit pattern

A DE often wants to keep the NULL rows but flag them. Never silently drop data during profiling. You don't need any new syntax for the flag: a predicate like email IS NULL is itself a value: in SQLite it evaluates to 1 when true and 0 when false. So you can drop an IS NULL test (or several joined with OR) straight into the SELECT list, beside a COALESCE display, and the row survives with its problem made visible:

SELECT
  customer_id,
  COALESCE(email, 'MISSING') AS email_display,
  (email IS NULL) AS email_is_missing        -- 1 when missing, else 0
FROM customers;

Join several tests with OR to flag "any key missing" in one 1/0 column: (email IS NULL OR region IS NULL).

Common pitfalls.

• = NULL / <> NULL are always unknown. Use IS NULL / IS NOT NULL.
• Aggregates and NOT IN treat NULL surprisingly; assume nothing.
• COALESCE(NULL, NULL) is still NULL. Provide a non-NULL final fallback if you need a guaranteed value.

Recap. NULL means "unknown"; comparisons to it are unknown, and WHERE drops unknown. Test with IS NULL/IS NOT NULL, default with COALESCE, and flag-don't-drop when auditing.