NULL: Unknown, Not Empty

Comparisons with NULL are unknown

NULL means "unknown" or "missing". Any comparison with NULL (=, <>, >) yields NULL, not true, so WHERE city <> 'Pune' silently skips rows where city is NULL. Use IS NULL, IS DISTINCT FROM (NULL-aware comparison), and coalesce() to supply defaults. count(*) counts rows but count(column) skips NULLs. Use NOT NULL wherever a value is always required, to avoid these surprises.

Where NULLs bite, run

I ran this with psql against PostgreSQL 16.2 (a local server started with the pgserver Python package), on a fresh database loaded with the shop schema from the first section. There are 4 customers but only 3 have a city. city <> 'Pune' returns only Ravi because John's NULL city is unknown; IS DISTINCT FROM includes John. NULL = NULL is itself NULL (shown blank), while coalesce supplies a fallback.

SELECT count(*) AS all_rows, count(city) AS with_city FROM customers;
SELECT name FROM customers WHERE city <> 'Pune';
SELECT name FROM customers WHERE city IS DISTINCT FROM 'Pune';
SELECT NULL = NULL AS null_equals_null, coalesce(NULL, 'fallback') AS coalesced;

Output:

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

 name 
------
 Ravi
(1 row)

 name 
------
 Ravi
 John
(2 rows)

 null_equals_null | coalesced 
------------------+-----------
                  | fallback
(1 row)

Default to NOT NULL

Make columns NOT NULL unless "unknown" is a real, meaningful state.

Quick check: What does WHERE city <> 'Pune' do with rows where city is NULL?

• Treats NULL as an empty string
• Includes them
• Raises an error
• Excludes them, because the comparison is unknown