COALESCE/NULLIF
COALESCE picks the first non-null value and NULLIF turns a chosen value into NULL.
In this page:
Syntax
SELECT COALESCE(column1, column2, default_value);
SELECT NULLIF(column, value);COALESCE/NULLIF
COALESCE(a, b, c) returns the first argument that is not NULL, making it ideal for defaults. NULLIF(a, b) returns NULL if a equals b and a otherwise, which prevents divide-by-zero errors when used as a divisor.
Combine them to safely calculate ratios.
Note:
Divide by NULLIF(x, 0) to avoid division-by-zero errors.
Example: COALESCE/NULLIF
CREATE TABLE stats (id INTEGER PRIMARY KEY, name TEXT, nickname TEXT, hits INTEGER, visits INTEGER);
INSERT INTO stats VALUES (1, 'Ada', NULL, 50, 10), (2, 'Bob', 'B', 0, 0), (3, 'Cy', NULL, 30, NULL);
SELECT name, COALESCE(nickname, name) AS display_name FROM stats;
SELECT name, hits * 1.0 / NULLIF(visits, 0) AS hits_per_visit FROM stats;
SELECT name, COALESCE(visits, 0) AS visits FROM stats;
-- Output:
-- name | display_name
-- Ada | Ada
-- Bob | B
-- Cy | Cy
-- name | hits_per_visit
-- Ada | 5.0
-- Bob | NULL
-- Cy | NULL
-- name | visits
-- Ada | 10
-- Bob | 0
-- Cy | 0Login to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Using COALESCE with mismatched types
- Forgetting NULLIF for divisors
- Treating empty text as NULL
Chapter Summary
- COALESCE returns the first non-null
- NULLIF returns NULL on equality
- Use NULLIF(x, 0) as a safe divisor
- Types must be compatible
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: