SQL NULL marks a value that is absent, unknown, or inapplicable. It is not an ordinary value: predicates involving NULL can evaluate to unknown, and WHERE retains only TRUE rows. Treat nullable columns as a query-design concern, not merely incomplete data. section 2.1
The NOT IN trap
A customer-state exclusion query can return no rows when its supplier-state subquery contains one NULL. Even a customer in Kansas, where no supplier exists, is excluded because state NOT IN ('Fake state', NULL) evaluates to unknown rather than TRUE. section 2.1
SELECT email, state
FROM erp.customer_contact_details
WHERE state NOT IN ('Fake state', null)
LIMIT 1;
email | state
-------+-------
(0 rows)
Compare the empty result from a NOT IN list containing NULL with the nonempty result from the equivalent list without it. section 2.1
For exclusion against a nullable subquery, use NOT EXISTS and explicitly decide whether NULL values from the outer table belong in the result. PostgreSQL can plan this form as a hash anti-join. NOT IN remains safe when a subquery cannot return NULL or when using known constant values. section 2.1.2
NULL also changes aggregates, arithmetic, and uniqueness:
- count(nullable_column) counts only non-NULL values; use count(*) when the goal is to count rows. section 2.6
- NULLIF can turn a zero divisor into NULL, while COALESCE can replace that NULL with a safe fallback value. section 2.5
- A composite unique key ordinarily treats rows containing NULL as distinct, so an ON CONFLICT upsert can insert duplicates instead of updating. section 2.8
If NULL should identify the same logical inventory location, PostgreSQL 15 offers a unique index with NULLS NOT DISTINCT. The safer broader design choice is often to avoid nullable columns in composite unique keys when possible. section 2.8 section 2.11
