NULL represents "unknown" or "missing" data. It behaves differently than you might expect.

Common NULL Pitfalls

1. NULL Comparisons Always Return NULL (Not False!)

-- Wrong ❌
SELECT * FROM users WHERE email = NULL;  -- Returns nothing

-- Correct ✅
SELECT * FROM users WHERE email IS NULL;

2. NULL in Math Operations

Any operation with NULL returns NULL:

SELECT 10 + NULL;        -- Returns NULL
SELECT NULL = NULL;      -- Returns NULL (not TRUE!)
SELECT NULL <> NULL;     -- Returns NULL (not FALSE!)

3. COUNT and Aggregates

-- COUNT(*) includes NULLs
SELECT COUNT(*) FROM users;           -- Returns 100

-- COUNT(column) excludes NULLs
SELECT COUNT(email) FROM users;       -- Returns 95 (5 have NULL email)

-- SUM, AVG, etc. ignore NULLs
SELECT AVG(age) FROM users;           -- Averages only non-NULL ages

Useful NULL Functions

COALESCE - Return First Non-NULL Value

SELECT name, COALESCE(phone, email, 'No contact') as contact
FROM users;

NULLIF - Return NULL if Values Match

-- Avoid division by zero
SELECT total / NULLIF(count, 0) as average
FROM stats;

IS DISTINCT FROM - NULL-Safe Comparison

-- Works correctly with NULLs
SELECT * FROM users
WHERE email IS DISTINCT FROM '[email protected]';