# NULL: Unknown, Not Empty — PostgreSQL

Source: https://www.skillbyai.com/en/postgresql/t-nulls

> Three-valued logic surprises.

## 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.

```sql
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.

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

- [ ] Treats NULL as an empty string
- [ ] Includes them
- [ ] Raises an error
- [x] Excludes them, because the comparison is unknown

*Answer:* Excludes them, because the comparison is unknown. Use IS DISTINCT FROM for NULL-aware comparisons.