Nulls and Three-Valued Logic

NULL Comparison

Unknown Is Not True

Comparing anything to NULL produces an unknown result, not true or false.

Program

Play the script to compare task dates with a selected day and see the missing date stay NULL.

target_day
null_comparison.sql
Replay: real traced execution (multi-file project)
CREATE TABLE tasks (name TEXT, completed_on TEXT);
INSERT INTO tasks VALUES ('write', '2026-02-01'), ('review', NULL), ('ship', '2026-02-02');
WITH params(target_day) AS (VALUES ('2026-02-02')) SELECT name, completed_on, completed_on = (SELECT target_day FROM params) AS same_day FROM tasks ORDER BY name;

CREATE TABLE tasks (name TEXT, completed_on TEXT);
INSERT INTO tasks VALUES ('write', '2026-02-01'), ('review', NULL), ('ship', '2026-02-02');
WITH params(target_day) AS (VALUES ('2026-02-01')) SELECT name, completed_on, completed_on = (SELECT target_day FROM params) AS same_day FROM tasks ORDER BY name;

CREATE TABLE tasks (name TEXT, completed_on TEXT);
INSERT INTO tasks VALUES ('write', '2026-02-01'), ('review', NULL), ('ship', '2026-02-02');
WITH params(target_day) AS (VALUES ('2026-02-03')) SELECT name, completed_on, completed_on = (SELECT target_day FROM params) AS same_day FROM tasks ORDER BY name;

  1. tables ← 1 row

    1CREATE TABLE tasks (name TEXT, completed_on TEXT);2INSERT INTO tasks VALUES ('write', '2026-02-01'), ('review', NULL), ('ship', '2026-02-02');
    values this step1 rowtables

  2. tasks ← 3 rows

    1CREATE TABLE tasks (name TEXT, completed_on TEXT);2INSERT INTO tasks VALUES ('write', '2026-02-01'), ('review', NULL), ('ship', '2026-02-02');3WITH params(target_day) AS (VALUES ('2026-02-02')) SELECT name, completed_on, completed_on = (SELECT target_day FROM params) AS same_day FROM tasks ORDER BY name;
    values this step3 rowstasks

  3. result ← 3 rows

    2INSERT INTO tasks VALUES ('write', '2026-02-01'), ('review', NULL), ('ship', '2026-02-02');3WITH params(target_day) AS (VALUES ('2026-02-02')) SELECT name, completed_on, completed_on = (SELECT target_day FROM params) AS same_day FROM tasks ORDER BY name;
    values this step3 rowsresult

  1. tables ← 1 row

    1CREATE TABLE tasks (name TEXT, completed_on TEXT);2INSERT INTO tasks VALUES ('write', '2026-02-01'), ('review', NULL), ('ship', '2026-02-02');
    values this step1 rowtables

  2. tasks ← 3 rows

    1CREATE TABLE tasks (name TEXT, completed_on TEXT);2INSERT INTO tasks VALUES ('write', '2026-02-01'), ('review', NULL), ('ship', '2026-02-02');3WITH params(target_day) AS (VALUES ('2026-02-01')) SELECT name, completed_on, completed_on = (SELECT target_day FROM params) AS same_day FROM tasks ORDER BY name;
    values this step3 rowstasks

  3. result ← 3 rows

    2INSERT INTO tasks VALUES ('write', '2026-02-01'), ('review', NULL), ('ship', '2026-02-02');3WITH params(target_day) AS (VALUES ('2026-02-01')) SELECT name, completed_on, completed_on = (SELECT target_day FROM params) AS same_day FROM tasks ORDER BY name;
    values this step3 rowsresult

  1. tables ← 1 row

    1CREATE TABLE tasks (name TEXT, completed_on TEXT);2INSERT INTO tasks VALUES ('write', '2026-02-01'), ('review', NULL), ('ship', '2026-02-02');
    values this step1 rowtables

  2. tasks ← 3 rows

    1CREATE TABLE tasks (name TEXT, completed_on TEXT);2INSERT INTO tasks VALUES ('write', '2026-02-01'), ('review', NULL), ('ship', '2026-02-02');3WITH params(target_day) AS (VALUES ('2026-02-03')) SELECT name, completed_on, completed_on = (SELECT target_day FROM params) AS same_day FROM tasks ORDER BY name;
    values this step3 rowstasks

  3. result ← 3 rows

    2INSERT INTO tasks VALUES ('write', '2026-02-01'), ('review', NULL), ('ship', '2026-02-02');3WITH params(target_day) AS (VALUES ('2026-02-03')) SELECT name, completed_on, completed_on = (SELECT target_day FROM params) AS same_day FROM tasks ORDER BY name;
    values this step3 rowsresult
unknown `completed_on = target_day` is NULL when `completed_on` is NULL.
three-valued logic SQL conditions can be true, false, or unknown.
comparison A normal equality comparison does not turn NULL into false.