KITFORMA · EDITORIAL ANSWER
Why does WHERE value = NULL return no matching rows?
KitForma editorial guide. NULL represents an unknown or absent value, so ordinary equality does not evaluate to true when either operand is NULL.
Step-by-step answer
NULL represents an unknown or absent value, so ordinary equality does not evaluate to true when either operand is NULL.
Use
WHERE value IS NULL or IS NOT NULL. In PostgreSQL, IS NOT DISTINCT FROM provides null-aware equality when comparing two nullable expressions. Test missing values alongside zero and an empty string; those are different states. Do not replace all nulls with a sentinel merely to make comparisons easier: a real value can collide with the sentinel and change ordering or aggregation. State explicitly whether a missing value is allowed in the schema.
Minimal example:
-- Portable example; verified with SQLite.
WITH sample(value) AS (VALUES (NULL), (0), (1))
SELECT COUNT(*) AS missing_count FROM sample WHERE value IS NULL;
-- Expected: 1Sources and verification
Sources checked:
Scope: This editorial guide is based on the cited sources and tool behavior. A forum question or a query observed for our site does not establish market search volume, low competition, guaranteed rankings or inadequate answers elsewhere.
This editorial answer was prepared by KitForma with AI assistance. It is not presented as a real member question or an independent user review. Check the sources and the result with your own file; report corrections in the discussion.