Polyglot
› Languages › SQLite › nullability
SQLite § nullability

NULL semantics

NULL is the SQL representation of missing information: the column has no value, the value is unknown, the value is inapplicable. The treatment of NULL in SQL is an inheritance from the relational calculus and is the source of more programmer surprise than any other feature of the language. SQLite implements the standard three-valued logic of ISO SQL with two well-known practical departures (the equality of NULL to itself in some specialised contexts) and a small library of NULL-safe operators that are particularly useful in everyday code.

Three-valued logic

SQL’s logic is three-valued: a boolean expression evaluates to one of TRUE, FALSE, or NULL (the unknown value). The unknown value propagates through most expressions: if any operand of an arithmetic, comparison, or string operation is NULL, the result is NULL.

SELECT 1 + NULL;          -- NULL
SELECT 'a' || NULL;       -- NULL
SELECT 1 = NULL;          -- NULL  (not FALSE)
SELECT NULL = NULL;       -- NULL  (not TRUE)
SELECT NULL <> NULL;      -- NULL  (not TRUE)

The crucial consequence is that WHERE x = NULL is never satisfied — not because NULL fails to equal NULL, but because the expression evaluates to NULL, which WHERE treats as false.

Testing for NULL

The conventional NULL test is IS NULL / IS NOT NULL, which compares for missingness and never itself returns NULL.

SELECT name FROM customer WHERE phone IS NULL;
SELECT name FROM customer WHERE phone IS NOT NULL;

SQLite extends IS to be a NULL-safe equivalence operator: x IS y returns true if both are NULL or if both are equal non-NULL values. The form x IS NULL is the special case in which the right-hand side is the literal NULL.

SELECT 1 IS 1;           -- 1 (true)
SELECT 1 IS NULL;        -- 0 (false)
SELECT NULL IS NULL;     -- 1 (true)
SELECT NULL IS 1;        -- 0 (false)

IS DISTINCT FROM (added in 3.39) is the NULL-safe inequality counterpart and is the standard SQL spelling: x IS DISTINCT FROM y is true if the values are unequal or exactly one is NULL. x IS NOT DISTINCT FROM y is the negation and is equivalent to x IS y.

SELECT 1 IS DISTINCT FROM 1;        -- 0 (false)
SELECT 1 IS DISTINCT FROM NULL;     -- 1 (true)
SELECT NULL IS DISTINCT FROM NULL;  -- 0 (false)

The recommendation in new code is to use IS DISTINCT FROM for portability and IS for brevity; either is correct and they are equivalent under SQLite’s three-valued logic.

NULL-handling functions

Three small functions are the workhorses of NULL handling.

COALESCE

COALESCE(a, b, c, …) returns the first non-NULL argument or NULL if all arguments are NULL. It accepts any number of arguments and is the SQL standard.

SELECT COALESCE(nickname, first_name, 'anonymous') FROM customer;

IFNULL

IFNULL(a, b) is the two-argument form of COALESCE. It is an SQLite-specific synonym; COALESCE is preferred in portable code.

SELECT IFNULL(phone, 'no phone') FROM customer;

NULLIF

NULLIF(a, b) returns a if a differs from b, otherwise NULL. The conventional use is to convert a sentinel value (an empty string, a zero) back to NULL for storage:

INSERT INTO customer (name, phone)
VALUES ('Ada', NULLIF(?, ''));   -- empty string from a form becomes NULL

NULLIF(a, b) is equivalent to CASE WHEN a = b THEN NULL ELSE a END but is concise enough to be preferred.

NULL in aggregate functions

Aggregate functions ignore NULL by convention — except COUNT(*), which counts rows including NULL-bearing rows.

CREATE TABLE temperature (id INTEGER, value REAL);
INSERT INTO temperature VALUES (1, 20.0), (2, NULL), (3, 22.0);

SELECT COUNT(*)     FROM temperature;  -- 3 (includes NULL row)
SELECT COUNT(value) FROM temperature;  -- 2 (NULL excluded)
SELECT AVG(value)   FROM temperature;  -- 21.0  ((20 + 22) / 2)
SELECT SUM(value)   FROM temperature;  -- 42.0
SELECT MIN(value)   FROM temperature;  -- 20.0

SUM over an empty set or a set of all NULL returns NULL. TOTAL (an SQLite-specific aggregate) returns 0.0 instead, which is convenient when the absence of rows should not propagate through subsequent arithmetic. AVG of all NULL returns NULL.

NULL in ORDER BY

ORDER BY places NULLs first by default in ascending order and last in descending order. The NULLS FIRST / NULLS LAST modifiers (added in 3.30) override the default:

SELECT name, phone FROM customer ORDER BY phone NULLS LAST;

The default is sometimes counter-intuitive — a NULLS LAST ordering is more common in user interfaces, where missing values are typically presented last. The explicit modifier is the recommended form whenever the placement of NULL matters to the consumer of the result.

NULL in GROUP BY

GROUP BY treats NULL as a single group. All rows with NULL in the grouping column collapse into one summary row.

SELECT phone IS NULL AS missing_phone, COUNT(*)
FROM customer
GROUP BY missing_phone;
-- 0 | 12
-- 1 | 3

This is the convention prescribed by ISO SQL and is identical across major engines.

NULL in DISTINCT

SELECT DISTINCT treats NULL as a single distinct value. A column containing several NULLs returns one NULL in a DISTINCT projection.

INSERT INTO customer (phone) VALUES (NULL), (NULL), ('555-1234');
SELECT DISTINCT phone FROM customer;
-- NULL
-- 555-1234

NULL in UNIQUE indexes

A UNIQUE index permits multiple rows with NULL in the indexed column. The justification is that NULL means unknown, and two unknowns are not the same value — uniqueness should not be violated by missing information. Programmers expecting the strict-typing behaviour, in which UNIQUE forbids any duplicate including NULL, are sometimes surprised; the SQLite behaviour matches ISO SQL.

CREATE TABLE user (id INTEGER PRIMARY KEY, email TEXT UNIQUE);
INSERT INTO user (email) VALUES ('a@x'), (NULL), (NULL);   -- accepted
INSERT INTO user (email) VALUES ('a@x');                   -- rejected

If genuine uniqueness including NULL is required, the conventional remedy is a partial unique index (CREATE UNIQUE INDEX ux ON user(email) WHERE email IS NOT NULL) or a CHECK (email IS NOT NULL) constraint on the column.

NULL in primary keys

A PRIMARY KEY column of a conventional rowid table cannot contain NULL: SQLite enforces non-NULL on a primary key as it does in ISO SQL. The historical exception is INTEGER PRIMARY KEY in older versions of SQLite, which mistakenly permitted NULL; the bug has been preserved for backward compatibility but is patched in STRICT tables and in WITHOUT ROWID tables, both of which enforce the standard.

CREATE TABLE strict_pk (id INTEGER PRIMARY KEY, val TEXT) STRICT;
INSERT INTO strict_pk (id, val) VALUES (NULL, 'x');
-- Error: NOT NULL constraint failed: strict_pk.id

NULL in NOT IN subqueries

The most-cited NULL trap in SQL is the interaction of NOT IN with a subquery whose result contains NULL.

SELECT name FROM customer WHERE id NOT IN (SELECT cust_id FROM bouquet_order);

If bouquet_order.cust_id is nullable and any row contains NULL, the NOT IN test returns NULL for every customer, and the outer query returns no rows. The mechanical reason: id NOT IN (a, b, c, NULL) rewrites to id <> a AND id <> b AND id <> c AND id <> NULL, and the last conjunct is NULL, which propagates to the whole expression.

The remedy is to restrict the subquery to non-NULL values:

SELECT name FROM customer
WHERE id NOT IN (
    SELECT cust_id FROM bouquet_order WHERE cust_id IS NOT NULL
);

or to use NOT EXISTS, which is not subject to the same trap:

SELECT name FROM customer c
WHERE NOT EXISTS (SELECT 1 FROM bouquet_order o WHERE o.cust_id = c.id);

The recommendation in new code is NOT EXISTS over NOT IN whenever the subquery’s column might contain NULL.

NULL in foreign keys

The semantics of NULL in a FOREIGN KEY column are: the constraint is not enforced when the foreign-key column is NULL. A row with a NULL foreign-key column is permitted regardless of whether the parent table contains any matching key, on the reading that a NULL foreign key means no relationship. This is the convention of ISO SQL and is consistent across major engines.

CREATE TABLE department (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE employee (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department_id INTEGER REFERENCES department(id)
);

INSERT INTO employee (name, department_id) VALUES ('Ada', NULL);  -- accepted

If a non-NULL foreign key is required, the column must be declared NOT NULL:

department_id INTEGER NOT NULL REFERENCES department(id)

A NULL design discipline

The recommendations applicable across an SQLite schema:

  • For each nullable column, write down the meaning of NULL: missing, not yet known, not applicable. If no meaning is articulable, declare NOT NULL.
  • Prefer IS NULL / IS NOT NULL to = NULL (which is always wrong).
  • Prefer IS DISTINCT FROM to manual NULL-handling for inequality comparisons in WHERE clauses.
  • Prefer NOT EXISTS to NOT IN when the subquery’s column is nullable.
  • Use COALESCE (or IFNULL) at the boundary of a query — when projecting for display or composing a string — rather than scattered through arithmetic.
  • When a column has a sentinel value (an empty string, a zero), consider whether the underlying meaning is missing; if so, store NULL and use NULLIF at the input boundary.
  • Add NULLS FIRST or NULLS LAST to ORDER BY whenever the placement of NULL matters to the consumer.
  • For UNIQUE constraints that should also forbid duplicate NULL, use a partial unique index with WHERE col IS NOT NULL and a CHECK (col IS NOT NULL) constraint as a pair.

NULL is not a defect of SQL; it is the language’s encoding of a real and recurring phenomenon — that information is sometimes missing — and a programmer who treats it as a value to be reasoned about rather than an error to be eliminated will write SQL that behaves correctly under the partial information that real systems contain.