= SQL NULL
{c}
<SQL NULL> marks a missing or unknown value. Ordinary comparisons involving <SQL NULL>, including `NULL = NULL`, produce the <truth value> unknown. To test a scalar value for absence, use `IS NULL`; to compare while treating two missing values alike, use `IS NOT DISTINCT FROM`.<Comparison Functions and Operators in the PostgreSQL documentation>[version 18, section 9.2, null comparisons and predicates]{ref}
This <three-valued logic> changes query reasoning. `5 NOT IN (2, NULL)` is unknown: five differs from two, but the missing value might be five. A <relational selection> retains true conditions, so unknown does not pass the filter.<Subquery Expressions in the PostgreSQL documentation>[version 18, section 9.24.3, NOT IN with null inputs]{ref}
<Database aggregation> has operation-specific rules. On values `2, NULL, 4`, `COUNT(*)` counts three rows, `COUNT(value)` counts two non-null values and `AVG(value)` is three. With no input rows, `COUNT` returns zero while `AVG` and `SUM` return <SQL NULL>. Therefore a blanket rule that all <database aggregation> ignores <SQL NULL> is insufficient.<Aggregate Functions in the PostgreSQL documentation>[version 18, section 9.21, count variants and empty-input behavior]{ref}
Back to article page