SQL NULL Values
NULL means "no value" — not zero, not empty string, not false. It's a placeholder for missing data.
Testing for NULL
SQL
-- ✓ Correct SELECT * FROM customers WHERE phone IS NULL; SELECT * FROM customers WHERE phone IS NOT NULL; -- ✗ Wrong — these never match anything SELECT * FROM customers WHERE phone = NULL; SELECT * FROM customers WHERE phone <> NULL;
NULL in arithmetic and comparisons
| Expression | Result |
|---|---|
NULL + 1 | NULL |
NULL = NULL | NULL (not TRUE!) |
NULL OR TRUE | TRUE |
NULL AND TRUE | NULL |
COUNT(*) | Counts NULL rows. |
COUNT(col) | Skips NULL values. |
Replacing NULL on the way out
SQL
SELECT name, COALESCE(phone, 'n/a') AS phone FROM customers;
Tip: Treat NULL as a third logical state alongside TRUE/FALSE (this is called three-valued logic). It's the source of most "but my WHERE clause filtered too much" bugs.
Example
Exercise
Test for a NULL value the correct way.
WHERE phone
NULL
Two letters; never use = with NULL.
Discussion
Loading…