Description

Login
Back

Beginner · 12 min

Missing values and NULL

Test for missing values with IS NULL instead of equality.

Sample database

CREATE TABLE groups (id INTEGER PRIMARY KEY, label TEXT NOT NULL);
CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT, group_id INTEGER REFERENCES groups(id));
CREATE TABLE sessions (id INTEGER PRIMARY KEY, member_id INTEGER NOT NULL REFERENCES members(id), topic TEXT NOT NULL, minutes INTEGER NOT NULL CHECK(minutes > 0), completed INTEGER NOT NULL CHECK(completed IN (0, 1)), day TEXT NOT NULL);
INSERT INTO groups VALUES (1, 'Morning'), (2, 'Evening');
INSERT INTO members VALUES (1, 'Ada', 'Oslo', 1), (2, 'Bo', 'Rome', 1), (3, 'Cy', NULL, 2), (4, 'Dee', 'Oslo', 2);
INSERT INTO sessions VALUES
  (1, 1, 'Travel', 20, 1, '2026-01-01'),
  (2, 1, 'Food', 10, 0, '2026-01-02'),
  (3, 2, 'Travel', 30, 1, '2026-01-01'),
  (4, 2, 'Music', 15, 1, '2026-01-03'),
  (5, 3, 'Music', 25, 0, '2026-01-02'),
  (6, 3, 'Travel', 15, 1, '2026-01-04');

Test for missing data

NULL represents a missing or unknown value. Comparing it with ordinary equality produces unknown rather than true. WHERE keeps only true conditions, so use IS NULL to find a missing city.

SELECT name FROM members WHERE city IS NULL ORDER BY id;

Output

name
Cy

Select known values

IS NOT NULL selects rows with a known value. An empty string is a text value, not NULL. Decide whether your application treats blank text as missing and enforce that rule when data is written.

SELECT name, city FROM members WHERE city IS NOT NULL ORDER BY id;

Output

name | city
Ada | Oslo
Bo | Rome
Dee | Oslo

Put it into practice

  1. Read the sample tables and predict the result before running the query.
  2. Solve the task, compare the returned rows, and explain why the solution works.

Try it yourself

Code runs on this device. When you are signed in, drafts sync to your account.

Run queries on a fresh sample database. Table changes last for this run only; your query draft is saved separately. Results show column names followed by rows. NULL means a missing value.

Apply what you learned

Return the names of members whose city is missing, ordered by ID.

Show solution
SELECT name FROM members WHERE city IS NULL ORDER BY id;

IS NULL evaluates to true for the member whose city is missing.

Practice