NULL and three-valued logic

NULL is not a value. It is a marker that says "there is a value here and we do not know it". Everything else in this lesson follows from taking that literally.

Three truth values

A comparison with an unknown is itself unknown. So SQL logic has three results: true, false and unknown (which SQL shows as NULL). The connectives behave the way you would reason about a fact you cannot see:

• unknown AND false is false — one false side settles it.
• unknown AND true is unknown — it depends on the side you cannot see.
• unknown OR true is true; NOT unknown is still unknown.

Arithmetic and string concatenation with NULL give NULL as well.

1SELECT2  NULL = NULL                     AS null_eq_null,3  CAST(NULL AS boolean) AND FALSE AS unknown_and_false,4  CAST(NULL AS boolean) AND TRUE  AS unknown_and_true,5  CAST(NULL AS boolean) OR TRUE   AS unknown_or_true,6  NOT CAST(NULL AS boolean)       AS not_unknown,7  1 + NULL                        AS arithmetic,8  'abc' || NULL                   AS concat;

Three clauses act on these results, and they do not all treat unknown the same way. WHERE, HAVING and ON keep a row only when the condition is true; unknown is dropped exactly like false. A CHECK constraint does the opposite and rejects only false, which the constraints lesson comes back to.

NOT IN and the NULL in the list

x NOT IN (a, b) means x <> a AND x <> b. If one of the values in the list is NULL, one of those comparisons is unknown, and the whole AND can never be true. The query returns no rows at all — no error, no warning.

1CREATE TABLE customers (2  id      int PRIMARY KEY,3  name    text NOT NULL,4  country text5);6INSERT INTO customers VALUES7  (1, 'Nino', 'GE'),8  (2, 'Hans', 'DE'),9  (3, 'Piotr', 'PL'),10  (4, 'Ghost', NULL);

1SELECT2  (SELECT COUNT(*) FROM customers3    WHERE country NOT IN ('DE'))       AS not_in_de,4  (SELECT COUNT(*) FROM customers5    WHERE country NOT IN ('DE', NULL)) AS not_in_de_or_null,6  (SELECT COUNT(DISTINCT country)7    FROM customers)                    AS count_distinct,8  (SELECT COUNT(*)9    FROM (SELECT DISTINCT country FROM customers) AS d) AS distinct_rows;

The same example shows the quieter half: a row whose own country is NULL never passes NOT IN ('DE') either. And it shows that grouping is different from comparing: DISTINCT and GROUP BY put all the NULLs in one group, while COUNT(DISTINCT country) skips them altogether.

Comparing two columns that can both be NULL

"Has the bonus changed from its target?" sounds like bonus <> target. It is not: when either side is NULL the answer is unknown, and the row disappears. Standard SQL has a comparison that treats two NULLs as equal and a NULL against a value as different:

WHERE bonus IS DISTINCT FROM target

PostgreSQL and SQL Server 2022 support it; MySQL spells the opposite test NOT (bonus <=> target). Where neither is available, write it out:

WHERE bonus <> target
   OR (bonus IS NULL AND target IS NOT NULL)
   OR (bonus IS NOT NULL AND target IS NULL)

1CREATE TABLE staff (2  id     int PRIMARY KEY,3  name   text NOT NULL,4  bonus  int,5  target int6);7INSERT INTO staff VALUES8  (1, 'Nino', 100, 100),9  (2, 'Ana', NULL, NULL),10  (3, 'Luka', NULL, 50),11  (4, 'Mari', 0, NULL),12  (5, 'Giorgi', 70, 80);

1SELECT2  name,3  bonus <> target                            AS not_equal,4  bonus IS DISTINCT FROM target              AS is_distinct,5  COALESCE(bonus, 0) <> COALESCE(target, 0)  AS coalesced6FROM staff7ORDER BY id;

COALESCE(a, b, ...) returns its first non-null argument, and it is the right tool when a missing value really does mean a default. It is the wrong tool for comparisons: COALESCE(bonus, 0) <> COALESCE(target, 0) decides that "no bonus" equals "a bonus of zero", which is a business decision hidden inside a filter — Mari's row in the example above.

Common mistakes

• NOT IN against a subquery that can return NULL. One NULL in the subquery's column and the result is empty. Filter the NULLs out of the subquery, or use NOT EXISTS, which the subqueries lesson shows is immune to this.
• <> on a nullable column. WHERE status <> 'closed' silently drops every row whose status is NULL; add OR status IS NULL if those rows belong in the answer.
• Replacing NULL with a sentinel to make a comparison work. COALESCE(x, 0) merges "unknown" with a real zero, as Mari's row shows.
• Expecting COUNT(DISTINCT col) to count the NULL group. It does not; SELECT DISTINCT does.