NULL handling
NULL means "unknown or missing", and it behaves differently from zero or empty text.
In this page:
Syntax
WHERE column IS NULL
WHERE column IS NOT NULL
SELECT COALESCE(column, default_value)NULL handling
Any arithmetic or comparison with NULL yields NULL, so use IS NULL and IS NOT NULL to test it. COALESCE(a, b, ...) returns the first non-null value, NULLIF(a, b) returns NULL when the values are equal, and aggregates such as COUNT(column) skip NULLs while COUNT(*) does not.
Note:
COUNT(column) ignores NULLs but COUNT(*) counts every row.
Example: NULL handling
CREATE TABLE contacts (id INTEGER PRIMARY KEY, name TEXT, phone TEXT, age INTEGER);
INSERT INTO contacts VALUES (1, 'Ada', '555-1', 36), (2, 'Bob', NULL, NULL), (3, 'Cy', NULL, 41);
SELECT name FROM contacts WHERE phone IS NULL;
SELECT name, COALESCE(phone, 'no phone') AS phone FROM contacts;
SELECT COUNT(*) AS rows, COUNT(phone) AS phones, AVG(age) AS avg_age FROM contacts;
SELECT name, age + 1 AS next_year FROM contacts;
-- Output:
-- name
-- Bob
-- Cy
-- name | phone
-- Ada | 555-1
-- Bob | no phone
-- Cy | no phone
-- rows | phones | avg_age
-- 3 | 1 | 38.5
-- name | next_year
-- Ada | 37
-- Bob | NULL
-- Cy | 42Login to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Using = NULL
- Expecting NULL + 1 to be 1
- Forgetting NOT IN with NULLs returns nothing
Chapter Summary
- NULL means unknown
- Comparisons with NULL are NULL
- COALESCE substitutes defaults
- COUNT(column) skips NULL
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: