NULL, expressions & CASE

A missing measurement is different from a score of zero. SQL represents missing or unknown values with NULL. It is not an empty string, the word "NULL", or a magic negative number. Model that distinction explicitly so a pending measurement does not become a failed trial by accident.

SQL predicates have three results: true, false and unknown. WHERE keeps only true rows. Most ordinary comparisons with NULL produce unknown. Even NULL = NULL is unknown; use IS NULL and IS NOT NULL to test for missing values.

Follow the data

start / 3

Use three-valued logic, distinguish missing from empty, and compute explicit result columns.

CASE builds a result value from ordered conditions. SQLite evaluates the first matching WHEN branch; if none matches, ELSE supplies the fallback. A CASE without a matching branch or ELSE returns NULL. In the worked example, test for a missing score first, then apply the pass threshold, then classify the remaining known scores as retry.

COALESCE returns its first non-NULL argument. It is useful for a display fallback, but it changes the meaning when used inside a predicate. Replacing a missing score with zero may be the wrong policy when you need to distinguish pending and failed. Use it only when the contract says which missing values should become which defaults.

Empty text remains text. A reviewer name of '' is preserved by COALESCE, as is the literal string 'NULL'. Column aliases make the output contract readable: the CASE result is status, while the reviewer expression keeps the reviewer column name.

Trace a small input

Read the columns together before predicting the query result.

Work a complete example

The example includes its own schema and data. Every Run creates a new SQLite database, so changing or rerunning it cannot change your other exercises.

SQL

CREATE TABLE attempts(id INTEGER PRIMARY KEY, score INTEGER, reviewer TEXT);
INSERT INTO attempts VALUES (4,NULL,NULL),(1,80,'Ada'),(3,79,''),(2,0,NULL);

SELECT id,
       CASE WHEN score IS NULL THEN 'pending'
            WHEN score >= 80 THEN 'pass' ELSE 'retry' END AS status,
       COALESCE(reviewer, 'unassigned') AS reviewer
FROM attempts
ORDER BY id;

Check the boundary or result

The values make the intended policy visible.

Diagnose the common failure

score = NULL never selects the missing-score branch. Another common mistake treats an empty reviewer as missing, silently discarding an intentional value. Tests distinguish NULL, empty text and the text NULL, so a query must preserve all three meanings. Boolean NOT does not turn unknown into true: NOT unknown remains unknown.

Try a change before building

Add a known score of 100 and a missing score with reviewer Lin. Work out both computed columns. Next, remove the ELSE branch and identify which known scores now produce NULL status. Restore it before building the full report.

Move into practice

Use three-valued logic, distinguish missing from empty, and compute explicit result columns. Checks test the rules; bug hunts ask you to identify a faulty line before seeing its test evidence. The fill-in keeps most of a working statement, and the build asks for the full contract. Public and hidden fixtures vary inputs, preserve duplicate counts and distinguish NULL from text. SQL results appear as tables; predict-output checks use compact JSON rows, one row per line, with null for SQL NULL.

Use the Watch & read resources for the lecture and primary SQLite reference. Reading or watching alone does not grant mastery. These are SQLite exercises; PostgreSQL, MySQL and SQL Server differ in types, syntax, query plans and concurrency behavior.