← SQL Foundations for Data Engineering

▶ Play from here · 36 min

Starts this lesson and continues through 4 more to the end of the course.

NULL and three-valued logic

The one-line version

NULL is not a value. It is the absence of knowledge. Every confusing thing NULL does follows from that, and once you hold it, none of it is surprising any more.

If you remember one sentence: you cannot compare an unknown to anything, including another unknown.

The rule

"Ordinary comparison operators yield null (signifying "unknown"), not true or false, when either input is null. For example, 7 = NULL yields null, as does 7 <> NULL." — PostgreSQL: Comparison Functions and Operators (verified 2026-08-12)

And the instruction that follows it:

"Do not write expression = NULL because NULL is not "equal to" NULL. (The null value represents an unknown value, and it is not known whether two unknown values are equal.)"

So instead of two truth values you have three: true, false, and unknown. A WHERE clause keeps rows where the condition is true — not "not false". Unknown rows are dropped, silently.

That single asymmetry is the source of most NULL bugs.

The two boolean rows are worth pausing on. NULL AND false is false, because it doesn't matter what the unknown is — the result is false either way. NULL OR true is true for the same reason. Three-valued logic isn't arbitrary; it's "what can we conclude without knowing?"

The NOT IN trap

This is the single most common NULL bug in production SQL, and a favourite interview question.

-- Looks correct. Returns zero rows if any customer_id in the subquery is NULL.
SELECT *
FROM orders
WHERE customer_id NOT IN (SELECT customer_id FROM blocked_customers);

Why. x NOT IN (a, b, NULL) expands to x <> a AND x <> b AND x <> NULL. That last term is unknown, and true AND unknown is unknown — so the whole condition is never true, and the row is dropped. Every row. You get an empty result set from a query that looks obviously right.

⚠️ Note the asymmetry with IN. x IN (a, b, NULL) still returns rows, because true OR unknown is true. So IN mostly works and NOT IN catastrophically doesn't — which is exactly why this survives testing.

Three fixes, in order of preference:

-- 1. NOT EXISTS — correct, and usually the best plan
SELECT * FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM blocked_customers b WHERE b.customer_id = o.customer_id
);

-- 2. LEFT JOIN ... IS NULL — the anti-join, explicit
SELECT o.* FROM orders o
LEFT JOIN blocked_customers b ON b.customer_id = o.customer_id
WHERE b.customer_id IS NULL;

-- 3. Filter the NULLs out of the subquery — works, but you must remember forever
SELECT * FROM orders
WHERE customer_id NOT IN (
  SELECT customer_id FROM blocked_customers WHERE customer_id IS NOT NULL
);

Prefer NOT EXISTS. It is correct by construction rather than by remembering, and it doesn't break the day somebody makes that column nullable.

IS DISTINCT FROM — the operator nobody teaches

When you genuinely want NULL to behave like an ordinary value — comparing two versions of a row, building a change-data-capture diff — there's a dedicated operator:

"For non-null inputs, IS DISTINCT FROM is the same as the <> operator. However, if both inputs are null it returns false, and if only one input is null it returns true. Similarly, IS NOT DISTINCT FROM is identical to = for non-null inputs, but it returns true when both inputs are null, and false when only one input is null. Thus, these predicates effectively act as though null were a normal data value, rather than "unknown"." — PostgreSQL, same page

This is the right tool for "did this column change?" in a SCD Type 2 or CDC merge:

-- Correct change detection, including transitions to and from NULL
WHERE new.email      IS DISTINCT FROM old.email
   OR new.phone      IS DISTINCT FROM old.phone
   OR new.updated_at IS DISTINCT FROM old.updated_at

Written with <> instead, a row whose email went from NULL to a real address would evaluate to unknown, and you would miss the change. That's a silent data-quality bug in a dimension table, and it is very hard to find later.

⚠️ Dialect note: IS DISTINCT FROM is standard SQL and documented in PostgreSQL. I have not verified its availability in every engine you might use — check your engine's documentation before relying on it. Where it is missing, the portable equivalent is (a <> b) OR (a IS NULL) <> (b IS NULL), or a COALESCE to a sentinel value that cannot occur in the data (and be careful: choosing a sentinel that can occur reintroduces the bug you were fixing).

NULL in aggregates

Aggregates ignore NULL, and that is usually what you want — until it isn't.

"except for count, these functions return a null value when no rows are selected. In particular, sum of no rows returns null, not zero as one might expect" — PostgreSQL: Aggregate Functions (verified 2026-08-12)

Two consequences that reach dashboards:

1. SUM over no rows is NULL, not 0. A revenue tile filtered to a region with no sales shows blank, not zero. Wrap it: COALESCE(SUM(amount), 0).

2. AVG ignores NULLs rather than treating them as zero. AVG of (10, NULL, 20) is 15, not 10. Whether that's right depends entirely on whether NULL means "no value recorded" or "zero" — which is a data-modelling question, not a SQL question. Decide it explicitly.

-- These are three different questions. Know which one you're asking.
AVG(score)                        -- mean of recorded scores
AVG(COALESCE(score, 0))           -- mean treating missing as zero
SUM(score) / COUNT(*)             -- same as above, written confusingly

COUNT gets its own lesson (lesson 3) because it deserves one.

Where NULLs come from

Worth naming, because most NULL bugs are introduced somewhere other than the source data:

• LEFT JOIN misses — the whole right side is NULL. This is by far the biggest source.
• CASE with no ELSE — an unmatched row silently becomes NULL. Always write an explicit ELSE, even if it's ELSE NULL, so the intent is visible.
• Type casts that fail softly, depending on engine.
• Outer-joined aggregates — SUM over a LEFT JOIN with no matches gives NULL, per the rule above.
• Union of tables with different schemas, where a column exists on one side only.

Check yourself

1. Why does WHERE status <> 'shipped' drop rows where status is NULL?
2. SELECT COUNT(*) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM vip) returns 0. Orders has 4 million rows. What happened?
3. You're writing SCD Type 2 change detection. Why is <> wrong?
4. A revenue dashboard tile is blank for one region rather than showing 0. Why?
5. Is NULL AND false unknown?

Answers

1. Because NULL <> 'shipped' is unknown, not true, and WHERE only keeps rows where the condition is true. To include them: WHERE status IS DISTINCT FROM 'shipped', or WHERE status <> 'shipped' OR status IS NULL.
2. At least one customer_id in vip is NULL. NOT IN expands to a chain of ANDed <> comparisons; the NULL one is unknown, so no row can satisfy the whole condition. Use NOT EXISTS.
3. Because a change from NULL to a value (or the reverse) evaluates to unknown, so the row isn't flagged as changed and the update is silently missed. Use IS DISTINCT FROM.
4. SUM over zero rows returns NULL, not 0. Wrap it in COALESCE(SUM(x), 0).
5. No — it's false. The unknown can't change the outcome, because false AND anything is false. This is the one case where three-valued logic collapses back to a definite answer.

Teaching this section

• Open with the NOT IN query and ask what it returns. Almost everyone says "the orders from non-blocked customers". Then reveal it returns nothing. That five-second gap is worth more than twenty minutes of truth tables.
• Demo: run SELECT NULL = NULL and let people see NULL rather than true or false.
• Good discussion: "In our warehouse, does NULL mean unknown or does it mean zero?" Teams usually discover they have both, in the same column, from different source systems. That's a real finding.
• Watch for: COALESCE used as a reflex. Coalescing to 0 before a AVG changes the answer; coalescing to '' before a join changes matching. It's a modelling decision each time.
• Timing: 20 min presented, 15 discussion.