T-SQL by Example
10 / 10

NULLs & 3-Valued Logic

NULL behavior, COALESCE/ISNULL, comparisons and pitfalls.

What you’ll learn

  • How NULL participates in comparisons (unknown).
  • IS NULL, COALESCE, and ISNULL.
-- Three-valued logic example
SELECT *
FROM dbo.Customers
WHERE MiddleName = N''  -- won't match NULLs
   OR MiddleName IS NULL;

-- COALESCE to fallback
SELECT COALESCE(MiddleName, N'(none)') AS M
FROM dbo.Customers;

Notes

  • Comparisons with NULL yield UNKNOWN, which filters out in WHERE.
  • COALESCE is ANSI; ISNULL(expr, repl) is T‑SQL-specific.