NULL and 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.

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:

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

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