Null Values

The previous lesson said “or it is null” twice: a foreign key may be null, a SET NULL action clears a column. What a null value means was left undefined. This lesson’s question is: what is a null value, what do comparisons made against it return, and what traps does adding a third result to the true–false pair open in query writing?

A Null Value Is Not a Value

Null is the marker showing that no value is present in a column. It is not zero, not an empty string, and not a special value named “none.” Its meaning reads as “that information does not exist for this row,” and it represents two separate situations under a single marker: unknown information (a member’s phone number exists but was not recorded) and inapplicable information (a loan transaction’s return date has not yet come into existence). That these two situations share the same marker is one of the oldest criticisms leveled at the relational model.

Because it is not a value, a null value cannot be compared against a value. The result of the comparison is neither true nor false:

sqlite3 :memory: <<'SQL'
.headers on
.mode box
.nullvalue (null)
SELECT NULL = NULL             AS is_equal,
       NULL <> NULL            AS is_different,
       NULL IS NULL            AS is_null,
       (NULL = NULL) IS NULL   AS result_is_null;
SQL

┌──────────┬──────────────┬─────────┬────────────────┐
│ is_equal │ is_different │ is_null │ result_is_null │
├──────────┼──────────────┼─────────┼────────────────┤
│ (null)   │ (null)       │ 1       │ 1              │
└──────────┴──────────────┴─────────┴────────────────┘

NULL = NULL is not true; NULL <> NULL is not true either. Both return a third result. The only correct way to test for a null value is the IS NULL and IS NOT NULL operators — these are not comparisons but state checks, and they always return true or false.

The Third Truth Value

The name of the third result a comparison can produce is unknown, and the name of the system built around it is three-valued logic. The AND and OR operators are extended to cover this third value as well. There is no need to memorize the table; having it computed is enough:

sqlite3 :memory: <<'SQL'
.headers on
.mode box
.nullvalue (null)
WITH d(label, v) AS (VALUES ('true', 1), ('false', 0), ('unknown', NULL))
SELECT a.label AS left_side, b.label AS right_side,
       a.v AND b.v AS both_true, a.v OR b.v AS either_true
FROM d a, d b;
SQL

┌───────────┬────────────┬───────────┬─────────────┐
│ left_side │ right_side │ both_true │ either_true │
├───────────┼────────────┼───────────┼─────────────┤
│ true      │ true       │ 1         │ 1           │
│ true      │ false      │ 0         │ 1           │
│ true      │ unknown    │ (null)    │ 1           │
│ false     │ true       │ 0         │ 1           │
│ false     │ false      │ 0         │ 0           │
│ false     │ unknown    │ 0         │ (null)      │
│ unknown   │ true       │ (null)    │ 1           │
│ unknown   │ false      │ 0         │ (null)      │
│ unknown   │ unknown    │ (null)    │ (null)      │
└───────────┴────────────┴───────────┴─────────────┘

The rule to read from the table is this: if one side settles the result, the unknown does not propagate. false AND unknown is false, because if one side is false the result is false no matter the other side. true OR unknown is true, for the same reason. In every other case, the unknown carries through to the result. This is the logical-level counterpart of the short-circuit evaluation seen in the Programming Fundamentals course.

The Set a Filter Splits

WHERE passes only the rows that yield true; false and unknown are filtered out together. The visible consequence is that two conditions assumed to be each other’s negation do not, together, cover every row:

sqlite3 :memory: <<'SQL'
.headers on
.mode box
CREATE TABLE loan (loan_no INTEGER PRIMARY KEY, member_no INTEGER, return_date TEXT);
INSERT INTO loan VALUES (1001, 41, '2025-03-16'), (1002, 52, NULL),
                        (1003, 41, '2025-03-19'), (1004, 41, NULL);
SELECT (SELECT COUNT(*) FROM loan) AS total,
       (SELECT COUNT(*) FROM loan WHERE return_date =  '2025-03-16') AS equal_count,
       (SELECT COUNT(*) FROM loan WHERE return_date <> '2025-03-16') AS different_count,
       (SELECT COUNT(*) FROM loan WHERE return_date IS NULL) AS null_count;
SQL

┌───────┬─────────────┬─────────────────┬────────────┐
│ total │ equal_count │ different_count │ null_count │
├───────┼─────────────┼─────────────────┼────────────┤
│ 4     │ 1           │ 1               │ 2          │
└───────┴─────────────┴─────────────────┴────────────┘

There are four rows; “equals” returns one row and “does not equal” returns one row. Their sum does not reach four, because two rows pass neither filter. The two-valued-logic habit of “a condition or its negation covers the whole set” does not hold in a column that holds null values. If full coverage is wanted, the condition must be written for it explicitly.

The NOT IN Trap

The most expensive consequence of the same rule shows up in subqueries. The phrasing x NOT IN (a, b, c) is equivalent to x <> a AND x <> b AND x <> c. If the list contains even a single null value, that comparison returns unknown, the AND chain falls into unknown, and no row passes:

sqlite3 :memory: <<'SQL'
.headers on
.mode box
CREATE TABLE member  (member_no INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE penalty (penalty_no INTEGER PRIMARY KEY, member_no INTEGER);
INSERT INTO member  VALUES (41, 'Alice Kane'), (52, 'Marcus Reyes'), (63, 'Sylvia Renner');
INSERT INTO penalty VALUES (9001, 52), (9002, NULL);

SELECT 'NOT IN, raw subquery' AS phrasing, COUNT(*) AS rows_returned
  FROM member WHERE member_no NOT IN (SELECT member_no FROM penalty)
UNION ALL
SELECT 'NOT IN, nulls filtered', COUNT(*)
  FROM member WHERE member_no NOT IN (SELECT member_no FROM penalty WHERE member_no IS NOT NULL)
UNION ALL
SELECT 'NOT EXISTS', COUNT(*)
  FROM member m WHERE NOT EXISTS (SELECT 1 FROM penalty p WHERE p.member_no = m.member_no);
SQL

┌────────────────────────┬───────────────┐
│        phrasing        │ rows_returned │
├────────────────────────┼───────────────┤
│ NOT IN, raw subquery   │ 0             │
│ NOT IN, nulls filtered │ 2             │
│ NOT EXISTS             │ 2             │
└────────────────────────┴───────────────┘

The correct answer is two: two members have no penalty. The first phrasing returns zero, and it does this without raising an error — the query is valid, the result is silently wrong. This is the most commonly encountered null-value trap, and it has two defenses: either the null values are filtered out inside the subquery, or the NOT EXISTS phrasing is chosen. The second is preferred, because its correctness does not depend on the subquery’s content.

Uniqueness and Null Values

A uniqueness constraint follows the same logic. Because two null values are not considered equal to one another, a unique column can hold more than one null value:

sqlite3 :memory: <<'SQL'
.headers on
.mode box
CREATE TABLE member (
  member_no   INTEGER PRIMARY KEY,
  name        TEXT NOT NULL,
  national_id TEXT UNIQUE
);
INSERT INTO member VALUES (41, 'Alice Kane', NULL);
INSERT INTO member VALUES (52, 'Marcus Reyes', NULL);
INSERT INTO member VALUES (63, 'Sylvia Renner', NULL);
SELECT COUNT(*) AS row_count FROM member;
SQL

┌───────────┐
│ row_count │
├───────────┤
│ 3         │
└───────────┘

All three rows passed. This behavior conforms to the standard, but it can vary by engine; some systems allow only a single null value in a unique column. The practical consequence of this ambiguity is that if a column is meant to be both unique and required, UNIQUE alone is not enough — NOT NULL must be added alongside it. The reverse also holds: a uniqueness constraint cannot be used to limit the number of null values.

Aggregate Functions

Aggregate functions do not count null values into their totals. COUNT(*) counts rows, COUNT(column) counts the rows where that column holds a value; SUM and AVG skip null values:

sqlite3 :memory: <<'SQL'
.headers on
.mode box
.nullvalue (null)
CREATE TABLE penalty (penalty_no INTEGER PRIMARY KEY, amount INTEGER);
INSERT INTO penalty VALUES (9001, 30), (9002, NULL), (9003, 10);
SELECT COUNT(*) AS row_count, COUNT(amount) AS filled_amount,
       SUM(amount) AS total, AVG(amount) AS average FROM penalty;
SQL

┌───────────┬───────────────┬───────┬─────────┐
│ row_count │ filled_amount │ total │ average │
├───────────┼───────────────┼───────┼─────────┤
│ 3         │ 2             │ 40    │ 20.0    │
└───────────┴───────────────┴───────┴─────────┘

The average is the average of two values, not of three rows. If a null value should count as zero, that must be written explicitly; skipping is what the function does on its own. Whether null values sort first or last is left unspecified by the standard and varies by engine; if a specific position is wanted, it must be declared explicitly in the sort expression.

Avoiding Null Values

A null value is a tool, not a goal. It shows up unnecessarily in three common situations, and all three have a modeling counterpart.

• Columns set aside for a multivalued fact. The phone_1, phone_2, phone_3 columns sit null in most rows for two of the three. The counterpart is moving phone numbers into a separate relation.
• Columns meaningful only for some rows. An end date specific to a fixed-term membership sits null for members with no fixed term. The counterpart is keeping the subtype in a separate relation.
• An event that has not yet occurred. A null return date means the loan transaction is still in progress. This is the right use of a null value — there is no other marker to announce that the event has not occurred.

The general criterion is this: a null value is in its place when it states a condition; it calls for fixing the schema when it is papering over incomplete modeling.

Summary

• A null value is not a value; it is distinct from zero and from an empty string, and it is tested only with IS NULL.
• Comparisons produce a third result; WHERE passes only what is true, so a condition and its negation do not together cover every row.
• A single null value in a NOT IN subquery silently empties the result; NOT EXISTS does not carry this sensitivity.
• A uniqueness constraint treats null values as distinct from one another; if a value is required, NOT NULL must be written separately.
• Aggregate functions skip null values; an average divides by the count of values, not the count of rows.

Next Step

This lesson showed the consequences of a column being allowed to hold a null value; what the column holds remains unaddressed. Dates were written as text and amounts as integers throughout the course, and these choices were never justified. The next lesson takes up number, text, date, and binary types; it discusses why a monetary amount is not kept in a floating-point number, what setting a text comparison depends on, and whether large binary data belongs in a database at all.