The UNKNOWN Problem: NULL Traps in T-SQL, Series Contents

A nine-part series on NULL traps in T-SQL.

A woman explorer tests the tiles ahead with a long pole as she crosses a floor where some tiles are ghostly and some have opened into trap doors.

Each post shows one place where three-valued logic turns a comparison into UNKNOWN and changes a result without raising an error: MERGE change detection, NOT IN, inequality filters, CHECK constraints, aggregates, string concatenation, ISNULL versus COALESCE, unique keys and joins. The last part shows INTERSECT and EXCEPT, where NULL compares the way most people expect.

  1. The MERGE That Would Not Update: A NULL in Your Change Detection
  2. NOT IN With a NULL Returns Nothing
  3. The Inequality Filter That Drops Your NULL Rows
  4. The CHECK Constraint That Lets NULL Through
  5. COUNT, AVG, and the NULLs Your Aggregates Ignore
  6. One NULL Turns the Whole Concatenation Into NULL
  7. ISNULL vs COALESCE: Not as Interchangeable as They Look
  8. One NULL in a Unique Key, and None Across a Join
  9. INTERSECT and EXCEPT: Where NULL Finally Behaves

Questions or corrections are welcome on Bluesky or LinkedIn. See all series on this blog.