Finding NULL Values

SQLNULL handlingJuniorMidUpdated Nov 10, 2026

Return every user who has not added a phone number. A skipped phone is stored as NULL.

Schema

CREATE TABLE users (
  id     BIGINT PRIMARY KEY,
  email  TEXT NOT NULL,
  phone  TEXT  -- NULL when the user skipped it at signup
);

Drill This: Your Query First

Answer, Then Reveal

Write the query before you look at the answer.

Skip to Reading

The Correct Query

Option (b)

SELECT id, email
FROM users
WHERE phone IS NULL;

NULL means unknown, so phone = NULL evaluates to NULL for every row, and WHERE keeps only rows where the condition is true: (a) returns nothing. IS NULL is the test that matches missing values. (c) looks for empty strings, which Postgres treats as a different value from NULL. (d) returns users who do have a phone, and it also skips the NULL rows, since NULL <> '' is unknown too.

Going Deeper

IS DISTINCT FROM is the NULL-safe version of <>: it treats two NULLs as equal and a NULL as different from any value. If the app sometimes writes '' for a skipped field, normalize it with NULLIF(phone, '') or a CHECK constraint so the column has one way to say missing.

The Options That Fail

The explanation above says why each of these breaks.

Option (a)

SELECT id, email
FROM users
WHERE phone = NULL;

Option (c)

SELECT id, email
FROM users
WHERE phone = '';

Option (d)

SELECT id, email
FROM users
WHERE phone <> '';