NULL means a value is missing or unknown. It isn't zero and it isn't an empty string, and it behaves differently from both:
- Any comparison with NULL is unknown, never true.
WHERE email = NULL returns no rows at all, even when emails are missing. Use IS NULL and IS NOT NULL.
- Arithmetic with NULL gives NULL:
5 + NULL is NULL.
- Aggregates skip NULLs.
COUNT(email) counts filled-in emails; COUNT(*) counts rows. AVG divides by the number of non-NULL values.
COALESCE(a, b, ...) returns the first value that isn't NULL. It's the standard way to fill gaps in a report: COALESCE(loyalty_tier, 'None'). The fallback should be the same type as the column: text for text, a number for numbers.
In customers, some people never gave an email or joined a loyalty tier. In orders, shipped_date stays NULL until an order ships, and most orders have no coupon_code.