NULL and Three-Valued Logic

TRUE, FALSE and UNKNOWN

NULL means "unknown", so comparisons with NULL return UNKNOWN (shown as empty), not true or false: NULL = NULL is unknown. WHERE keeps only rows where the condition is TRUE, so unknown rows silently disappear. Use IS NULL, IS DISTINCT FROM (a NULL-safe comparison) and COALESCE for defaults. Aggregates skip NULLs: count(city) counts non-null values while count(*) counts rows, and city <> 'Pune' excludes NULL cities.

NULL comparisons and counting, run

I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). Empty cells are NULL (unknown) results. 2 IN (1, NULL) is unknown rather than false. John has no city, so he is counted by count(*) but not count(city), and not matched by city <> 'Pune'.

SELECT NULL = NULL AS eq, NULL IS NULL AS is_null,
       NULL IS DISTINCT FROM NULL AS distinct_from, 1 IN (1, NULL) AS in_hit,
       2 IN (1, NULL) AS in_miss, COALESCE(NULL, 'fallback') AS coalesced;

SELECT count(*) AS all_rows, count(city) AS with_city,
       count(*) FILTER (WHERE city <> 'Pune') AS not_pune
FROM customers;

Output:

 eq | is_null | distinct_from | in_hit | in_miss | coalesced 
----+---------+---------------+--------+---------+-----------
    | t       | f             | t      |         | fallback
(1 row)

 all_rows | with_city | not_pune 
----------+-----------+----------
        4 |         3 |        1
(1 row)

Ask "what if this is NULL?" for every predicate

Especially for <>, NOT IN and outer-joined columns.

Quick check: What does NULL = NULL evaluate to?

• An error
• TRUE
• FALSE
• UNKNOWN (NULL)