NULL represents the absence of a value — unknown or not applicable — and it behaves differently from zero or an empty string. Testers must handle it deliberately because ordinary comparisons against NULL do not work as intuition expects.
The core trap is three-valued logic: any comparison with NULL using =, <>, <, or > yields UNKNOWN, not TRUE or FALSE. So WHERE column = NULL never matches anything, and crucially WHERE column <> 'active' silently EXCLUDES rows where the column is NULL — a very common validation bug. To test for null you must use IS NULL / IS NOT NULL. NULL also affects aggregates (COUNT(column) ignores NULLs while COUNT(*) counts all rows), and it propagates through arithmetic (NULL + 5 = NULL). COALESCE(column, fallback) returns the first non-null value and is the standard way to substitute a default. For testers, NULLs are frequent defect sources: a field that should have been populated is NULL, or a filter accidentally drops NULL rows. Treat nullable columns as a first-class concern in every validation.
A tester checks for users who haven't verified their email with WHERE verified_at <> '' and gets zero rows, missing the bug — because unverified rows have verified_at = NULL, not an empty string. WHERE verified_at IS NULL correctly finds them.
-- WRONG: never matches, because = NULL is UNKNOWN
SELECT id FROM users WHERE deleted_at = NULL;
-- RIGHT: test for null explicitly
SELECT id FROM users WHERE deleted_at IS NULL; -- active users
SELECT id FROM users WHERE deleted_at IS NOT NULL; -- soft-deleted users
-- The exclusion trap: this DROPS rows where status is NULL
SELECT id FROM orders WHERE status <> 'cancelled';
-- Include NULLs explicitly if intended:
SELECT id FROM orders WHERE status <> 'cancelled' OR status IS NULL;
-- Substitute a default for reporting
SELECT id, COALESCE(country, 'UNKNOWN') AS country FROM users;Scenario: A tester validates that no orders are in the 'cancelled' state after a cleanup job by running SELECT COUNT(*) FROM orders WHERE status <> 'cancelled' and comparing it to the total row count. The counts match, so they pass the test — but cancelled orders actually remain. What went wrong?