NULL Handling

NULL Handling

What it means

NULL represents “unknown” or “absent” — it’s not zero, not an empty string, and critically, NULL = NULL evaluates to UNKNOWN (not TRUE), so you must test for it with IS NULL/IS NOT NULL rather than =. ISNULL(value, replacement) and COALESCE(value1, value2, ...) both substitute a fallback when a value is NULL — COALESCE is the ANSI-standard version and accepts more than two arguments.

Examples

SELECT * FROM Customers WHERE Email IS NULL;
SELECT * FROM Customers WHERE Email IS NOT NULL;

-- WRONG way to test for NULL -- this never matches, even for NULL rows
-- SELECT * FROM Customers WHERE Email = NULL;

SELECT Name, ISNULL(Phone, 'No phone on file') AS PhoneDisplay FROM Customers;

-- COALESCE returns the first non-NULL value from the list
SELECT Name, COALESCE(MobilePhone, HomePhone, WorkPhone, 'No phone') AS BestContact
FROM Customers;

-- NULL propagates through arithmetic: any calculation involving NULL is NULL
SELECT 5 + NULL;   -- returns NULL, not 5

Common mistake

Testing for NULL with = NULL instead of IS NULL — this is a very common trap, since it looks correct but always evaluates to UNKNOWN (treated as false), silently returning zero rows even when NULL values exist.

-- Returns NO rows, even if some customers genuinely have a NULL Email
SELECT * FROM Customers WHERE Email = NULL;

-- Correct
SELECT * FROM Customers WHERE Email IS NULL;

Quick practice

1. Why does WHERE Email = NULL never match any rows, even ones where Email is actually NULL?

   Answer

   In SQL, comparing anything to NULL with = evaluates to UNKNOWN, not TRUE — you must use IS NULL to correctly test for NULL values.

2. What’s the difference between ISNULL() and COALESCE()?

   Answer

   Functionally similar for two arguments, but COALESCE() is ANSI-standard SQL and accepts any number of arguments, returning the first non-NULL one; ISNULL() is SQL Server-specific and takes exactly two.

3. What does 5 + NULL evaluate to?

   Answer

   NULL — NULL propagates through arithmetic operations rather than being treated as zero.