Nulls and Three-Valued Logic

IS NULL

Find Missing Values

IS NULL and IS NOT NULL test for missing values explicitly.

Program

Play the script to choose whether the query returns missing or present due dates.

want_missing
is_null_filter.sql
Replay: real traced execution (multi-file project)
CREATE TABLE tickets (id INTEGER, title TEXT, due_on TEXT);
INSERT INTO tickets VALUES (1, 'docs', NULL), (2, 'release', '2026-03-10'), (3, 'cleanup', NULL);
WITH params(want_missing) AS (VALUES (1)) SELECT id, title, due_on FROM tickets WHERE ((SELECT want_missing FROM params) = 1 AND due_on IS NULL) OR ((SELECT want_missing FROM params) = 0 AND due_on IS NOT NULL) ORDER BY id;

CREATE TABLE tickets (id INTEGER, title TEXT, due_on TEXT);
INSERT INTO tickets VALUES (1, 'docs', NULL), (2, 'release', '2026-03-10'), (3, 'cleanup', NULL);
WITH params(want_missing) AS (VALUES (0)) SELECT id, title, due_on FROM tickets WHERE ((SELECT want_missing FROM params) = 1 AND due_on IS NULL) OR ((SELECT want_missing FROM params) = 0 AND due_on IS NOT NULL) ORDER BY id;

  1. tables ← 1 row

    1CREATE TABLE tickets (id INTEGER, title TEXT, due_on TEXT);2INSERT INTO tickets VALUES (1, 'docs', NULL), (2, 'release', '2026-03-10'), (3, 'cleanup', NULL);
    values this step1 rowtables

  2. tickets ← 3 rows

    1CREATE TABLE tickets (id INTEGER, title TEXT, due_on TEXT);2INSERT INTO tickets VALUES (1, 'docs', NULL), (2, 'release', '2026-03-10'), (3, 'cleanup', NULL);3WITH params(want_missing) AS (VALUES (1)) SELECT id, title, due_on FROM tickets WHERE ((SELECT want_missing FROM params) = 1 AND due_on IS NULL) OR ((SELECT want_missing FROM params) = 0 AND due_on IS NOT NULL) ORDER BY id;
    values this step3 rowstickets

  3. result ← 2 rows

    2INSERT INTO tickets VALUES (1, 'docs', NULL), (2, 'release', '2026-03-10'), (3, 'cleanup', NULL);3WITH params(want_missing) AS (VALUES (1)) SELECT id, title, due_on FROM tickets WHERE ((SELECT want_missing FROM params) = 1 AND due_on IS NULL) OR ((SELECT want_missing FROM params) = 0 AND due_on IS NOT NULL) ORDER BY id;
    values this step2 rowsresult

  1. tables ← 1 row

    1CREATE TABLE tickets (id INTEGER, title TEXT, due_on TEXT);2INSERT INTO tickets VALUES (1, 'docs', NULL), (2, 'release', '2026-03-10'), (3, 'cleanup', NULL);
    values this step1 rowtables

  2. tickets ← 3 rows

    1CREATE TABLE tickets (id INTEGER, title TEXT, due_on TEXT);2INSERT INTO tickets VALUES (1, 'docs', NULL), (2, 'release', '2026-03-10'), (3, 'cleanup', NULL);3WITH params(want_missing) AS (VALUES (0)) SELECT id, title, due_on FROM tickets WHERE ((SELECT want_missing FROM params) = 1 AND due_on IS NULL) OR ((SELECT want_missing FROM params) = 0 AND due_on IS NOT NULL) ORDER BY id;
    values this step3 rowstickets

  3. result ← 1 row

    2INSERT INTO tickets VALUES (1, 'docs', NULL), (2, 'release', '2026-03-10'), (3, 'cleanup', NULL);3WITH params(want_missing) AS (VALUES (0)) SELECT id, title, due_on FROM tickets WHERE ((SELECT want_missing FROM params) = 1 AND due_on IS NULL) OR ((SELECT want_missing FROM params) = 0 AND due_on IS NOT NULL) ORDER BY id;
    values this step1 rowresult
IS NULL `due_on IS NULL` finds missing due dates.
IS NOT NULL `due_on IS NOT NULL` finds present due dates.
WHERE A `WHERE` filter keeps only rows where the condition is true.