NULL and three-valued logic

Data is rarely complete. A customer skips the optional field, a sensor misses a reading, a rating was never given. SQL marks such a gap with NULL: not a value, but the note “there is no value here”. This lesson explains how NULL changes the rules of the lesson on WHERE, and the handful of tools that make queries behave when values are missing.

NULL means “not known”

NULL is not zero, not an empty string and not the word “NULL”. It says that a value is missing, and SQL treats a missing value as unknown. That one idea explains its behaviour: ask whether an unknown value equals 7 and the honest answer is “I don’t know”. Ask whether two unknown values are equal, and the answer is still “I don’t know”.

So a comparison with NULL is neither true nor false. Its result is a third truth value, unknown, which SQL represents with NULL again:

-- quote() shows a missing value as the word NULL (the result grid shows an empty cell).
SELECT quote(NULL = NULL)  AS eq,
       quote(NULL <> NULL) AS ne,
       quote(NULL IS NULL) AS is_null;

┌──────┬──────┬─────────┐
│  eq  │  ne  │ is_null │
├──────┼──────┼─────────┤
│ NULL │ NULL │ 1       │
└──────┴──────┴─────────┘

quote() is a SQLite function that writes a value the way you would type it in SQL, so a missing value appears as the word NULL. Without it, the result grid would show an empty cell, which looks the same as an empty string; this page uses quote() whenever the difference matters. The first two columns are unknown: NULL = NULL is not true, and NULL <> NULL is not true either. The last column uses the operator made for the job: IS NULL, like IS NOT NULL, is always true or false.

Three-valued logic

With three truth values, AND, OR and NOT need a rule for “unknown”. The rule is common sense: if the known part already settles the answer, the unknown part does not matter; otherwise the result is unknown too.

• FALSE AND unknown is false, because one false side is enough to make AND false.
• TRUE OR unknown is true, because one true side is enough to make OR true.
• TRUE AND unknown, FALSE OR unknown and NOT unknown stay unknown.

-- 1 is true, 0 is false and NULL is unknown.
CREATE TABLE pairs (a INTEGER, b INTEGER);
INSERT INTO pairs VALUES (1, 1), (1, 0), (1, NULL), (0, 0), (0, NULL), (NULL, NULL);

SELECT quote(a)       AS a,
       quote(b)       AS b,
       quote(a AND b) AS a_and_b,
       quote(a OR b)  AS a_or_b,
       quote(NOT a)   AS not_a
FROM pairs;

┌──────┬──────┬─────────┬────────┬───────┐
│  a   │  b   │ a_and_b │ a_or_b │ not_a │
├──────┼──────┼─────────┼────────┼───────┤
│ 1    │ 1    │ 1       │ 1      │ 0     │
│ 1    │ 0    │ 0       │ 1      │ 0     │
│ 1    │ NULL │ NULL    │ 1      │ 0     │
│ 0    │ 0    │ 0       │ 0      │ 1     │
│ 0    │ NULL │ 0       │ NULL   │ 1     │
│ NULL │ NULL │ NULL    │ NULL   │ NULL  │
└──────┴──────┴─────────┴────────┴───────┘

1 is true and 0 is false, as in the lesson on WHERE. The table is the same in PostgreSQL, MySQL and SQLite; only the spelling differs, since PostgreSQL shows its boolean values as t and f.

What WHERE does with unknown

WHERE keeps a row only when its condition is true. A false condition drops the row, and so does an unknown one:

The consequence is easy to miss. A condition and its opposite do not always cover every row between them:

CREATE TABLE reviews (
  id      INTEGER PRIMARY KEY,
  product TEXT NOT NULL,
  rating  INTEGER          -- 1 to 5; NULL when the buyer gave no rating
);
INSERT INTO reviews VALUES
  (1, 'Ghee 500 ml', 5),   (2, 'Atta 10 kg', NULL),
  (3, 'Jaggery 1 kg', 3),  (4, 'Poha 500 g', 4),
  (5, 'Honey 250 g', NULL), (6, 'Rasam powder', 2);

SELECT count(*) AS all_rows         FROM reviews;
SELECT count(*) AS four_or_more     FROM reviews WHERE rating >= 4;
SELECT count(*) AS not_four_or_more FROM reviews WHERE NOT (rating >= 4);
SELECT count(*) AS unrated          FROM reviews WHERE rating IS NULL;

┌──────────┐
│ all_rows │
├──────────┤
│ 6        │
└──────────┘
┌──────────────┐
│ four_or_more │
├──────────────┤
│ 2            │
└──────────────┘
┌──────────────────┐
│ not_four_or_more │
├──────────────────┤
│ 2                │
└──────────────────┘
┌─────────┐
│ unrated │
├─────────┤
│ 2       │
└─────────┘

Six reviews, two with four stars or more and two with fewer, which accounts for only four rows. The two reviews without a rating are in neither group, because both rating >= 4 and NOT (rating >= 4) are unknown for them. When a query must account for every row, add the missing ones explicitly: rating < 4 OR rating IS NULL.

The same thing happens with <>. WHERE coupon <> 'FESTIVE10' does not return the orders without a coupon, which often surprises people who wanted “every order except those with this coupon”.

NOT IN and a NULL in the list

x NOT IN (a, b) is shorthand for x <> a AND x <> b. If one of the values in the list is NULL, one of those comparisons is unknown for every row. AND can then be false, or unknown, but never true, so the query returns nothing at all:

CREATE TABLE orders (id INTEGER PRIMARY KEY, coupon TEXT);
INSERT INTO orders VALUES (1, 'FESTIVE10'), (2, NULL), (3, 'WELCOME'), (4, 'FESTIVE10');

-- Orders that did not use the coupon FESTIVE10.
SELECT id FROM orders WHERE coupon NOT IN ('FESTIVE10');

-- The same, with a NULL that slipped into the list.
SELECT count(*) AS with_null_in_list FROM orders WHERE coupon NOT IN ('FESTIVE10', NULL);

┌────┐
│ id │
├────┤
│ 3  │
└────┘
┌───────────────────┐
│ with_null_in_list │
├───────────────────┤
│ 0                 │
└───────────────────┘

The first query also shows the row-level effect: order 2 has no coupon, so NULL NOT IN ('FESTIVE10') is unknown, and only order 3 is returned. In practice the NULL rarely appears in a typed list; it sneaks in from a subquery that returns a list of values, which the subqueries module covers along with NOT EXISTS, the usual fix.

Testing for NULL

Because = NULL is never true, a query that tests for missing values with it finds nothing, without an error. This is the most common NULL mistake:

CREATE TABLE customers (
  id          INTEGER PRIMARY KEY,
  first_name  TEXT NOT NULL,
  middle_name TEXT   -- NULL: not known; '': known to have none
);
INSERT INTO customers VALUES
  (1, 'Aditi',  'Rani'),
  (2, 'Bilal',  NULL),
  (3, 'Chitra', ''),
  (4, 'Dev',    '   ');

-- The common mistake: "= NULL" is never true, so this counts nothing.
SELECT count(*) AS equals_null FROM customers WHERE middle_name = NULL;

SELECT first_name FROM customers WHERE middle_name IS NULL;

-- The grid shows NULL, '' and spaces alike; typeof() and quote() do not.
SELECT first_name,
       middle_name,
       typeof(middle_name) AS type,
       quote(middle_name)  AS quoted
FROM customers;

┌─────────────┐
│ equals_null │
├─────────────┤
│ 0           │
└─────────────┘
┌────────────┐
│ first_name │
├────────────┤
│ Bilal      │
└────────────┘
┌────────────┬─────────────┬──────┬────────┐
│ first_name │ middle_name │ type │ quoted │
├────────────┼─────────────┼──────┼────────┤
│ Aditi      │ Rani        │ text │ 'Rani' │
│ Bilal      │             │ null │ NULL   │
│ Chitra     │             │ text │ ''     │
│ Dev        │             │ text │ '   '  │
└────────────┴─────────────┴──────┴────────┘

middle_name = NULL counts nothing; middle_name IS NULL finds Bilal. The last table shows why an empty string must not be confused with NULL. Chitra’s middle name is known to be empty, Bilal’s is not known, and Dev’s is three spaces, yet the result grid shows all three the same way. typeof() reports the storage class (null or text) and quote() makes the difference visible.

In PostgreSQL, MySQL and SQLite an empty string is a value like any other, and '' is never NULL. Oracle Database is the well-known exception: it treats a string of length zero as null, which matters if you ever move data there.

Comparing values that may be missing

Sometimes you want NULL to count as an ordinary value: “has the city changed?” should be yes when it went from unknown to Surat, and no when both are unknown. The standard SQL operators for that are IS DISTINCT FROM and IS NOT DISTINCT FROM, which treat two NULLs as equal and never return unknown:

-- A customer's city before and after an address change; NULL means not known.
CREATE TABLE address_changes (id INTEGER PRIMARY KEY, old_city TEXT, new_city TEXT);
INSERT INTO address_changes VALUES
  (1, 'Pune', 'Pune'),  (2, 'Pune', 'Nagpur'), (3, NULL, 'Surat'),
  (4, NULL, NULL),      (5, 'Agra', NULL);

SELECT id,
       quote(old_city)                     AS old_city,
       quote(new_city)                     AS new_city,
       quote(old_city <> new_city)         AS not_equal,
       old_city IS DISTINCT FROM new_city  AS is_distinct,
       old_city IS NOT new_city            AS sqlite_is_not
FROM address_changes;

┌────┬──────────┬──────────┬───────────┬─────────────┬───────────────┐
│ id │ old_city │ new_city │ not_equal │ is_distinct │ sqlite_is_not │
├────┼──────────┼──────────┼───────────┼─────────────┼───────────────┤
│ 1  │ 'Pune'   │ 'Pune'   │ 0         │ 0           │ 0             │
│ 2  │ 'Pune'   │ 'Nagpur' │ 1         │ 1           │ 1             │
│ 3  │ NULL     │ 'Surat'  │ NULL      │ 1           │ 1             │
│ 4  │ NULL     │ NULL     │ NULL      │ 0           │ 0             │
│ 5  │ 'Agra'   │ NULL     │ NULL      │ 1           │ 1             │
└────┴──────────┴──────────┴───────────┴─────────────┴───────────────┘

<> is unknown for rows 3, 4 and 5, so a WHERE old_city <> new_city would miss the changes in rows 3 and 5. IS DISTINCT FROM gives the intended answer for every row. SQLite also has short forms: a IS b means a IS NOT DISTINCT FROM b, and a IS NOT b means a IS DISTINCT FROM b. MySQL spells the null-safe equality a <=> b, which its manual describes as the same as the standard IS NOT DISTINCT FROM.

Replacing missing values: COALESCE and NULLIF

Two functions do most of the everyday work with missing values, and both are part of standard SQL:

• coalesce(a, b, …) returns its first argument that is not NULL. It is the usual way to show a default: coalesce(note, '(no note)').
• nullif(a, b) returns NULL when a equals b, and a otherwise. It turns a stand-in value back into a missing one, such as an empty string or a zero.

Together they answer two common questions. Should an empty note count as no note? Then turn '' into NULL before choosing the default. Will a division fail when the divisor is zero? Then divide by nullif(divisor, 0), and the result is NULL instead:

CREATE TABLE pages (
  page   TEXT PRIMARY KEY,
  visits INTEGER,           -- NULL when the counter was broken
  orders INTEGER NOT NULL,
  note   TEXT
);
INSERT INTO pages VALUES
  ('home',    1200, 36, 'festival banner'),
  ('offers',  0,     0, NULL),
  ('recipes', NULL,  5, '');

SELECT page,
       coalesce(note, '(no note)')             AS note_or_default,
       coalesce(nullif(note, ''), '(no note)') AS blank_too,
       quote(orders * 100.0 / nullif(visits, 0)) AS conversion_pct
FROM pages;

-- SQLite on its own: dividing by zero gives NULL, not an error.
SELECT quote(5 / 0) AS int_division, quote(5.0 / 0) AS real_division;

┌─────────┬─────────────────┬─────────────────┬────────────────┐
│  page   │ note_or_default │    blank_too    │ conversion_pct │
├─────────┼─────────────────┼─────────────────┼────────────────┤
│ home    │ festival banner │ festival banner │ 3.0            │
│ offers  │ (no note)       │ (no note)       │ NULL           │
│ recipes │                 │ (no note)       │ NULL           │
└─────────┴─────────────────┴─────────────────┴────────────────┘
┌──────────────┬───────────────┐
│ int_division │ real_division │
├──────────────┼───────────────┤
│ NULL         │ NULL          │
└──────────────┴───────────────┘

The recipes page shows the difference between the two coalesce columns: its note is an empty string, not NULL, so only the version with nullif(note, '') shows the default. For offers, nullif(visits, 0) turns the zero into NULL, and the conversion rate becomes unknown instead of a division by zero.

The last table shows why that matters when you move between engines. Division by zero gives NULL in SQLite. In MySQL it also gives NULL, but with a warning under the default SQL mode of MySQL 8.4. PostgreSQL stops the whole query with an error. nullif(divisor, 0) makes the intent explicit and gives the same answer in all three.

One more difference: PostgreSQL requires all the arguments of coalesce to be convertible to one common data type, so a text default for a number column is an error there, while MySQL and SQLite accept a mix of types. Writing defaults of the column’s own type, such as coalesce(visits, 0) for a number, keeps a query portable.

Choose a default with care, though. Aggregate functions such as avg() and count(column) skip NULLs, so the average rating of the reviews above ignores the two unrated ones. avg(coalesce(rating, 0)) would count them as zero stars and pull the average down, which is a different question. The aggregates module looks at this closely.

Key takeaways

• NULL means a missing, unknown value; any ordinary comparison with it is unknown, even NULL = NULL.
• WHERE keeps only rows whose condition is true, so rows with NULL drop out of both a condition and its opposite.
• Test with IS NULL and IS NOT NULL; compare values that may be missing with IS [NOT] DISTINCT FROM (MySQL: <=>).
• An empty string is a value, not NULL, in PostgreSQL, MySQL and SQLite.
• coalesce() supplies a default, nullif() turns a stand-in value into NULL, and x / nullif(y, 0) avoids division-by-zero errors.
• A NULL in the list of a NOT IN makes the condition return no rows.