Part 4 of 5 · 1 min read
NULL in AND, OR, and COALESCE
How unknown propagates through logical operators, and how to supply a default.
NULL in AND, OR, and COALESCE
Think of a jury that can answer "guilty," "not guilty," or "cannot decide," and the rules for combining two such answers into one verdict.
In plain words: unknown AND true is unknown. unknown OR true is true. unknown AND false is false. The general rule: AND returns the most restrictive result, OR returns the most permissive, when unknown is involved. COALESCE returns the first non-NULL value from a list, commonly used to supply a readable default where NULL would otherwise appear.
A worked example. A query filtering discount IS NULL OR discount = 0 catches both missing and explicitly zero discounts. Displaying COALESCE(discount, 0) shows 0 instead of a blank for a book with no recorded discount, without changing what is actually stored.
The key lines.
SELECT title, COALESCE(discount, 0) AS discount
FROM books;
COALESCE
COALESCE returns the first non-NULL value from a list of expressions, left to right.
Example: COALESCE(discount, 0) shows 0 for display whenever the actual stored discount is NULL.
Remember this
= NULL and <> NULL never match anything, ever, no matter what is being compared. IS NULL and IS NOT NULL are the only correct tests. This single fact prevents the most common real NULL bug.
Quick notes
- NULL means unknown or missing, not zero and not an empty string.
- Comparing to NULL with = or <> returns unknown, not true or false.
- IS NULL and IS NOT NULL are the only correct way to test for NULL.
- AND with unknown returns the most restrictive result; OR with unknown returns the most permissive.
- COALESCE returns the first non-NULL value from a list, useful for display defaults.