# NULL in SQL: The Rules That Break Queries

> Three-valued logic, why NOT IN returns nothing, COUNT(*) vs COUNT(col), and how NULLs change joins and aggregates.

- Source: https://akshaythakare.com/notes/sql/null-handling
- Topic: SQL
- Level: beginner
- Reading time: 2 min
- Tags: sql, null, fundamentals, interview
- Updated: 2026-10-11

## NULL means unknown

Any comparison with NULL is neither true nor false. It is **unknown**, and `WHERE` keeps only rows where the condition is true.

```sql
SELECT * FROM customers WHERE city = NULL;    -- always zero rows
SELECT * FROM customers WHERE city IS NULL;   -- correct
```

## The NOT IN trap

```sql
-- returns NO rows if any blocked_id is NULL
SELECT * FROM users WHERE id NOT IN (SELECT blocked_id FROM blocks);

-- safe
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM blocks b WHERE b.blocked_id = u.id);
```

`id NOT IN (1, NULL)` becomes `id <> 1 AND id <> NULL`. The second part is unknown, so the whole condition is never true.

## Aggregates skip NULLs

| Expression | Counts / uses |
| --- | --- |
| `COUNT(*)` | Every row |
| `COUNT(col)` | Rows where `col` is not NULL |
| `SUM(col)`, `AVG(col)` | Non-NULL values only |

`AVG(discount)` over (10, NULL, 20) is 15, not 10. Decide whether a missing value means zero, and use `COALESCE(discount, 0)` if it does.

## NULLs in joins and grouping

- `a.key = b.key` never matches NULL keys, so rows with NULL keys vanish from inner joins.
- `GROUP BY` puts all NULLs into **one** group.
- `UNION` and `DISTINCT` treat NULLs as equal to each other.

> **Warning: Make NULL policy explicit**
>
> For every nullable column, write down what NULL means: unknown, not applicable, or zero. Most metric bugs come from mixing the three.