# NULL and three-valued logic

> NULL means unknown, so comparisons return unknown — and NOT IN with a NULL returns nothing.

Concept · Writing queries · https://levelcode.org/concepts/null-three-valued-logic

In SQL, `NULL` doesn't mean zero or empty. It means **unknown**. Any comparison with an unknown value is itself unknown, so SQL logic has three values: `TRUE`, `FALSE` and `UNKNOWN`.

```sql
SELECT NULL = NULL;     -- NULL (unknown), not true
SELECT NULL <> 1;       -- NULL
SELECT NULL AND FALSE;  -- false: false whatever the unknown is
SELECT NULL OR TRUE;    -- true
```

`WHERE`, `HAVING`, `ON` and `CHECK` constraints don't treat unknown the same way. The first three keep a row only when the condition is **true**, so `UNKNOWN` drops the row just like `FALSE`. A `CHECK` constraint only rejects `FALSE`, so `UNKNOWN` passes. That's why `CHECK (price > 0)` still allows a NULL price.

## The traps

- **`= NULL` never matches.** Use `IS NULL` / `IS NOT NULL`. To compare two columns where NULL should equal NULL, use `IS NOT DISTINCT FROM`.
- **`NOT IN` with a NULL in the list returns no rows.** `x NOT IN (1, NULL)` means `x <> 1 AND x <> NULL`, and the second part is always unknown. Subqueries hit this all the time:

```sql
-- Returns nothing if any order has a NULL customer_id
SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);

-- Safe version
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
```

- **Negating a filter doesn't give you "everything else."** `WHERE status <> 'done'` skips rows where `status` is NULL. Those rows are in neither half.
- **Aggregates skip NULLs.** `count(col)` counts non-NULL values while `count(*)` counts rows. `avg(col)` divides by the non-NULL count. `sum` over no rows (or only NULLs) returns NULL, not 0, so wrap it in `COALESCE(sum(x), 0)` when you need a number.
- **Outer joins create NULLs.** In a `LEFT JOIN`, a `WHERE` condition on a right-hand column removes exactly the unmatched rows you joined to keep. See [logical query order](https://levelcode.org/concepts/sql-logical-order).

## Designing with NULL

Make columns `NOT NULL` unless "unknown" is a real state of the data. A nullable boolean has three states, and every query that reads it has to handle all three. Where NULL is legitimate, such as an optional end date, say so in the schema and handle it on purpose with `COALESCE` or `IS NULL`. Don't find out from a query that quietly returned too few rows.