2 · Handle real values · 12 MIN

Understand unknown values and NULL

NULL means missing or unknown; it is different from zero and empty text.

Lin has zero points, which is a known number, but no known city, represented by NULL. Equality with NULL does not return true, even for another NULL. Use IS NULL to select missing values. COALESCE returns the first non-NULL argument and is useful for a display fallback; it does not update stored data. Decide whether a fallback is only presentation or changes the meaning of a report before using it in calculations.

Keep the source rows visible. Predict which rows and columns will remain after each operation.

Before you start

No installation. The tables below are loaded automatically for every run; edits do not touch the CodingNeed account database.

New words, explained

NULL: An unknown or absent value, not a number or a string.
COALESCE: Return the first non-NULL argument.
threeValuedLogic: SQL conditions can be true, false or unknown.

Follow the example step by step

Compare Lin’s points 0 with its city NULL.
Run IS NULL to isolate the missing value.
Try city = NULL and observe that it finds no rows.
Use IS NOT NULL for the exercise.

Practice table: learners
id	name	city	points
1	Ada	Delhi	20
2	Lin	NULL (unknown)	0
3	Grace	Delhi	35
4	Sam	Pune	20

Practice table: enrollments
learner_id	course
1	React
1	SQL
3	SQL

You are ready to move on when: Return name and city for learners with a known city, ordered by id. Use IS NOT NULL.

Read the example

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

Check the expected output

[["Lin"]]

Your challenge

Return name and city for learners with a known city, ordered by id. Use IS NOT NULL.

Common trap

The string 'NULL' is ordinary text and does not represent a missing value.

Open interactive exercise →See the syllabus

Further reading: SQLite SELECT reference

Next lesson: Sort ties and choose a small result →