Data Types & NULLs
Type coercion and NULL handling are the two silent sources of wrong numbers in otherwise-correct queries. A string/int comparison that silently casts, or a NULL that short-circuits an expression, will pass every syntax check and still return garbage.
Type conversion #
- CAST: explicit type conversion (
CAST(x AS TYPE)/x::TYPE).
NULL handling #
- IS NULL / IS NOT NULL —
= NULLnever returns true; use these. - COALESCE: return the first non-NULL from a list.
- IFNULL / NULLIF — the two-argument variants, and the inverse (
NULLIFturns a sentinel value into NULL).
Warning
NULL is not zero, not empty string, not false. It's "unknown." NULL + 1 is NULL, NULL = NULL is NULL (not true), and SUM(col) skips them but COUNT(*) doesn't. Every NULL you ignore is a silent bug waiting for production data.