SQL NULL and Three-Valued Logic

SQL NULL represents missing or inapplicable information. It is different from zero and from an empty string. An ordinary comparison involving NULL, such as country = ‘KR’, evaluates to unknown rather than true or false. WHERE retains only true conditions.

Suppose three customers have countries KR, US, and NULL. Filtering for country = ‘KR’ finds one customer; country <> ‘KR’ also finds one. The two counts do not cover all three customers. Use IS NULL to report the third case, or explicitly include it in a category when the reporting definition calls for that. NOT unknown remains unknown; combining conditions with AND or OR follows three-valued logic.

COUNT(*) counts rows, while COUNT(country) counts non-NULL countries. AVG(amount) ignores NULL amounts, so its denominator can be smaller than the row count. This is a change in the measured population, not just a display choice.

COALESCE chooses the first non-NULL compatible expression. It returns NULL if all inputs are NULL and does not replace empty strings. A fallback of zero is a claim about meaning when used in a calculation. Preserve the distinction between an observed zero, no observation, and a failed conversion. For NULL-safe equality, DuckDB supports IS NOT DISTINCT FROM; that policy still needs to make sense for the keys being compared.

Reference: DuckDB documentation. Work through the examples in SQL from Zero to Working.