SQLite Operators and NULL: IS NULL, COALESCE, IFNULL

NULL Means "Unknown"

Every other value in SQLite represents something definite — a number, a string, a blob. NULL is different. It's a placeholder for a value that's missing or unknown. That single idea explains every weird thing NULL does in queries.

Set up a small table to play with:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT * FROM users;

Two columns allow nulls. Boris has no email. Cleo has no age. Dan has neither. The rest of the page is about querying rows like these without getting fooled.

= and <> Don't Work With NULL

Your first instinct is to write WHERE email = NULL. It looks reasonable. It returns nothing:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email FROM users WHERE email = NULL;

Zero rows — even though Boris and Dan clearly have null emails. The reason: comparing anything to NULL produces NULL, not true or false. SQLite's WHERE clause only keeps rows where the condition is true, and NULL isn't true. So the row is filtered out.

The same trap with <>:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email FROM users WHERE email <> 'ada@example.com';

You'd expect this to return everyone except Ada. It returns only Cleo. Boris and Dan, whose emails are null, get dropped — because NULL <> 'ada@example.com' is also NULL, not true.

This is the single most common SQL gotcha. Whenever a query "loses rows" you didn't expect, suspect a null column.

Use IS NULL and IS NOT NULL

The right way to test for null is the IS operator. Unlike =, it knows about null and returns true or false — never null:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name FROM users WHERE email IS NULL;

SELECT name FROM users WHERE email IS NOT NULL;

The first query returns Boris and Dan. The second returns Ada and Cleo. IS NULL and IS NOT NULL are the two operators built specifically to ask "is this value missing?" Use them anywhere you'd otherwise be tempted to write = NULL or <> NULL.

If you want "not Ada, including the unknowns," combine the checks explicitly:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email
FROM users
WHERE email <> 'ada@example.com' OR email IS NULL;

Now Boris, Cleo, and Dan all show up.

NULL Propagates Through Arithmetic and Concatenation

The "unknown" rule applies beyond comparisons. Any operation that touches a null produces a null:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    age,
    age + 1       AS next_year,
    age * 2       AS doubled,
    'Hi ' || name AS greeting,
    'Age: ' || age AS labelled_age
FROM users;

next_year and doubled are null for Cleo and Dan. labelled_age is null for them too — concatenating a string with NULL gives NULL, not 'Age: '. If a column might be null and you need a usable value out the other side, you have to handle it. That's where the next two functions come in.

IFNULL: Two-Argument Fallback

IFNULL(a, b) returns a unless it's null, in which case it returns b. It's the simplest way to swap a null for a default:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    IFNULL(email, '(no email)') AS email,
    IFNULL(age, 0)              AS age
FROM users;

Boris and Dan get (no email). Cleo and Dan get 0. The original data isn't changed — IFNULL just rewrites the output.

IFNULL always takes exactly two arguments. If you need more fallbacks, reach for COALESCE.

COALESCE: First Non-NULL Wins

COALESCE(a, b, c, ...) walks its arguments left to right and returns the first one that isn't null. It generalizes IFNULL to any number of fallbacks:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    COALESCE(email, name || '@unknown.local', 'anonymous') AS contact
FROM users;

For Ada and Cleo, the email is used. For Boris and Dan, the email is null, so SQLite tries the second argument — a synthesized address based on the name. If that were also null, it would fall through to 'anonymous'.

COALESCE is the portable choice — every major SQL database supports it the same way. IFNULL is a SQLite/MySQL convenience for the two-argument case. Pick COALESCE by default; reach for IFNULL only when you genuinely have just two arguments and want the shorter name.

NULL Is Not an Empty String

A common confusion: people treat NULL and '' as interchangeable. They're not.

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    '' IS NULL          AS empty_is_null,
    NULL IS NULL        AS null_is_null,
    length('')          AS empty_length,
    length(NULL)        AS null_length,
    '' = ''             AS empty_equals_empty,
    NULL = NULL         AS null_equals_null;

'' is a real string that happens to have zero characters. NULL is the absence of a value. length('') is 0; length(NULL) is itself NULL. And NULL = NULL is NULL, not 1 — which is exactly why IS NULL exists.

If a column can contain both '' and NULL, decide which one means "missing" and stick to it. Mixing them forces every query to handle two cases, and you will forget one.

NULL in IN, NOT IN, and DISTINCT

A few more places null sneaks up on you.

IN with a list that contains null can give surprising results, especially with NOT IN:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, age FROM users WHERE age NOT IN (25, NULL);

You might expect everyone whose age isn't 25. You get nothing. SQLite expands NOT IN (25, NULL) to roughly age <> 25 AND age <> NULL, and age <> NULL is always NULL — so the whole condition is never true. The fix is to filter nulls out of the list (or out of the column) before the comparison.

DISTINCT, on the other hand, treats nulls as equal to each other for the purpose of deduplication:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT DISTINCT email FROM users;

You get three rows: Ada's email, Cleo's email, and a single NULL (collapsed from Boris and Dan). The same goes for GROUP BY and UNION — they treat nulls as a single group, which is the opposite of how = treats them. SQL is not always consistent about this; it pays to know which side of the line each operator falls on.

A Quick Checklist

• Test for missing values with IS NULL / IS NOT NULL. Never = NULL.
• Any arithmetic, concatenation, or comparison touching NULL returns NULL.
• Use COALESCE(a, b, c, ...) to replace nulls with a fallback. Use IFNULL(a, b) for the two-argument shorthand.
• Empty string '' is not the same as NULL. Pick one for "missing" in each column.
• NOT IN (..., NULL) is almost always a bug. Strip nulls from the list first.

Next: Sorting Results

Once you can filter rows correctly — including the null ones — the next step is putting them in a useful order. ORDER BY is the next page, and it has its own opinions about where nulls land in a sorted result.