NULL Semantics in Queries

~12 min · queries, null

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Three-valued logic in practice

SQL has three truth values: TRUE, FALSE, and NULL ('unknown'). Any operation involving NULL evaluates to NULL — including NULL = NULL. WHERE only returns rows where the condition is TRUE, so NULLs silently drop out unless you handle them explicitly.

The traps

WHERE x = NULL never matches. Use IS NULL.
WHERE x <> 'something' excludes rows where x is NULL. Add OR x IS NULL if you want them.
NOT IN (subquery) returns zero rows if the subquery contains any NULL. Use NOT EXISTS.
Aggregates (SUM, AVG, COUNT(col)) silently skip NULL.

Tools to handle NULL deliberately

COALESCE(a, b, c) — first non-NULL value.
NULLIF(a, b) — NULL if a equals b (great for avoiding division by zero).
IS DISTINCT FROM / IS NOT DISTINCT FROM — NULL-safe equality.

Code

NULL surprises·sql

SELECT NULL = NULL;        -- NULL  (not TRUE)
SELECT NULL <> NULL;       -- NULL  (not FALSE)
SELECT 5 + NULL;           -- NULL
SELECT NULL OR TRUE;       -- TRUE  (NULL OR TRUE = TRUE)
SELECT NULL AND TRUE;      -- NULL
SELECT NULL = NULL IS TRUE;-- FALSE  (the IS handles three-valued logic)

Handling NULL in WHERE·sql

-- Include NULL discount in the negative case
SELECT * FROM products
WHERE  discount <> 0.5 OR discount IS NULL;

-- Or use IS DISTINCT FROM
SELECT * FROM products
WHERE  discount IS DISTINCT FROM 0.5;

-- Replace NULL with a fallback in display
SELECT name, COALESCE(phone, 'N/A') AS phone
FROM   contacts;

-- Avoid division by zero
SELECT revenue, expenses,
       revenue / NULLIF(expenses, 0) AS ratio
FROM   financials;

External links

PostgreSQL NULL semantics

Exercise

Write four small queries that exercise each NULL trap above, then rewrite each one safely. Bonus: contrive a query where switching from NOT IN to NOT EXISTS changes the result.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousRETURNING: PostgreSQL's Secret Weapon Next →CASE, COALESCE, and Conditional Logic

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.