NULL and three-valued logic

conceptThe concept

SQL uses three truth values: true, false and unknown (NULL). NULL = NULL is unknown, not true, and NOT unknown is still unknown. WHERE and JOIN conditions keep a row only when the condition is true, so rows where the comparison is unknown silently disappear.

Use IS NULL / IS NOT NULL to test for NULL, and IS DISTINCT FROM for a NULL-safe "not equal". COALESCE(a, b, ...) returns the first non-NULL argument; NULLIF(a, b) returns NULL when a equals b, which is handy to avoid division by zero.

Aggregates skip NULLs: count(col) counts non-NULL values while count(*) counts rows, and avg(col) divides by the non-NULL count. sum of no rows is NULL, not 0.

whyWhy it matters

A filter such as color <> 'red' quietly excludes products with no colour at all. In finance and compliance reports that is a wrong number, not a cosmetic issue.

Use caseReal-world scenario

In the shop, some products have no color attribute and the CEO has no manager. Both show up as NULL, and both trip up naive filters.

Try itExamples

Run and edit these in the interactive lesson.

SELECT NULL = NULL        AS equals,
       NULL IS NULL      AS is_null,
       NULL <> 1         AS not_equal,
       NULL IS DISTINCT FROM 1 AS distinct_from,
       NOT (NULL = 1)    AS negated;

SELECT count(*) AS all_products,
       count(attrs->>'color') AS with_color,
       count(*) FILTER (WHERE attrs->>'color' <> 'red') AS not_red_naive,
       count(*) FILTER (WHERE attrs->>'color' IS DISTINCT FROM 'red') AS not_red_correct
FROM products;

SELECT name,
       coalesce(attrs->>'color', 'unspecified') AS color,
       stock,
       round(price / NULLIF(stock, 0), 4) AS price_per_unit_in_stock,
       CASE WHEN stock = 0 THEN 'sold out'
            WHEN stock < 20 THEN 'low'
            ELSE 'ok' END AS stock_status
FROM products
ORDER BY stock
LIMIT 8;

SELECT name, title, coalesce(manager_id::text, '(none)') AS manager
FROM employees
WHERE manager_id IS NULL OR manager_id = 1
ORDER BY id;

Avoid / preferDo this, not that

SELECT name FROM employees WHERE manager_id = NULL;

SELECT name FROM employees WHERE manager_id IS NULL;

SELECT count(*) FROM products WHERE attrs ->> 'color' <> 'red';

SELECT count(*) FROM products WHERE attrs ->> 'color' IS DISTINCT FROM 'red';

PitfallsCommon mistakes

• Writing WHERE col = NULL. It is never true; use IS NULL.
• Using NOT IN (subquery) when the subquery can return NULL: the whole predicate becomes unknown and returns no rows. The subqueries lesson shows the safe NOT EXISTS form.
• Assuming sum() over no rows is 0. Wrap it: coalesce(sum(x), 0).

LimitsWhen not to use it

Do not let NULL mean several things at once (unknown, not applicable, zero, not yet loaded). If the distinction matters to the business, model it explicitly with a status column or a separate table.

SecuritySecurity notes

Policies and permission checks that compare against a missing setting evaluate to NULL and hide rows, which is safe. Negated checks such as NOT (owner = current_user_id()) become NULL too and can behave unexpectedly; test policies with NULL inputs.

ChallengeTry it yourself

SELECT coalesce(attrs ->> 'color', 'no colour') AS colour, count(*) AS products
FROM products
GROUP BY 1
ORDER BY 2 DESC, 1;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• SELECT
• Comparison operators and NULL