Description

Login
Back

Intermediate · 18 min

CASE and missing-value fallbacks

Build conditional result values without changing stored data.

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');

Choose a result branch

CASE returns the value from the first matching branch. ELSE supplies a fallback when no branch matches. It is an expression, so it can appear in a selected column or an aggregate.

SELECT id, CASE WHEN completed = 1 THEN 'Done' ELSE 'Planned' END AS status FROM sessions WHERE id <= 2 ORDER BY id;

Output

id | status
1 | Done
2 | Planned

Replace a missing result value

COALESCE returns its first non-null argument. It does not replace empty text and does not update stored rows. Use a fallback only when that meaning is appropriate for the result.

SELECT name, COALESCE(city, 'Unknown') AS city FROM members ORDER BY id;

Output

name | city
Ada | Oslo
Bo | Rome
Cy | Unknown
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 member names and cities, displaying Unknown for missing cities, ordered by ID.

Show solution
SELECT name, COALESCE(city, 'Unknown') AS city FROM members ORDER BY id;

Only the missing city uses the fallback; known city values remain unchanged.

Practice