# NULL and Three-Valued Logic — SQL Deep Dive

Source: https://www.skillbyai.com/en/sql-deep-dive/t-nulls

> Unknown is not false.

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

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

**Quiz:** What does NULL = NULL evaluate to?

- [ ] An error
- [ ] TRUE
- [ ] FALSE
- [x] UNKNOWN (NULL)

*Answer:* UNKNOWN (NULL). Use IS NULL or IS NOT DISTINCT FROM.