Brainlag
  1. Library
  2. Concepts
  3. SQL

NULL, the missing value

NULL means "unknown", not zero or empty. Why = NULL never matches, how IS NULL and COALESCE work, and what NULL does to COUNT and AVG.

SQL8 min

NULL means unknown

NULL is not 0 and not an empty string. It means "no value here, we do not know". A game nobody has reviewed yet has a critic_score of NULL; a session that was quit early has no score.

Because NULL is unknown, any comparison with it is unknown too. Is an unknown score bigger than 50? Unknown. Is it equal to NULL? Also unknown. WHERE only keeps rows where the test is true, so WHERE score = NULL keeps nothing, ever. The same goes for != NULL.

To ask about NULL, use the special tests IS NULL and IS NOT NULL.

NULL in maths and totals

  • Maths with NULL gives NULL: NULL + 10 is NULL.
  • Aggregates skip NULLs. COUNT(*) counts rows, COUNT(score) counts rows that have a score, and AVG(score) is the average of the scores that exist.
  • COALESCE(a, b) gives back the first value that is not NULL. COALESCE(score, 0) turns a missing score into 0 for display.

Run it: games without a critic score

Run it, then change one thing and run it again.

Tables games, players, sessions
games 30 rows, 7 columns
idtitlegenreyearpricemultiplayercritic_score
1Pocket Kingdomstrategy202114.99086
2Neon Driftracing202319.99193
3Hollow Pineshorror20209.99087
4Star Courieradventure202224.99086
5Block Partyparty20194.99190
6Tiny Tacticsstrategy202412.99195
7Moonlit Farmsimulation202113.99170
8Skyline Rushplatformer20187.99069
9Deep Signalhorror202417.99090
10Goal Machinesports202529.99188
11Paper Knightsrpg202219.99069
12Cloud Cafesimulation202311.99064

First 12 of 30 rows. Query the table to see the rest.

players 24 rows, 4 columns
idgamertagcountryjoined_on
1NoScopeNanUK2024-01-31
2xX_Toast_XxCanada2025-04-20
3LagIsMyFaultAustralia2024-11-30
4QuietKeyboardIreland2025-03-27
5BrbSnacksUSA2024-07-19
6MumSaysBedtimeUK2025-06-15
7ZeroDeathsSweden2024-08-27
8AFKAlexUSA2024-10-28
9PixelPriyaIndia2025-05-26
10CtrlAltDefeatUK2024-01-05
11SirLagsALotGermany2024-03-28
12MidnightMoUK2025-04-13

First 12 of 24 rows. Query the table to see the rest.

sessions 240 rows, 6 columns
idplayer_idgame_idplayed_onminutesscore
121102026-09-0190990
216222026-09-01901740
317172026-09-01401580
43172026-09-01302500
54172026-09-01901010
613222026-09-01201620
79152026-09-0190140
812302026-09-01201120
92162026-09-01901280
10822026-09-0225770
115222026-09-02151260
1212302026-09-02252080

First 12 of 240 rows. Query the table to see the rest.

query.sql to run
SELECT COUNT(*) AS sessions,
       COUNT(score) AS with_score,
       COUNT(*) - COUNT(score) AS no_score
FROM sessions;

SELECT title, critic_score, COALESCE(critic_score, 'not rated yet') AS shown
FROM games
WHERE critic_score IS NULL OR critic_score >= 93;

The unrated games list is empty

The store wants the title and genre of every game that has no critic score yet, to send them to reviewers. The query runs and returns nothing, but there are two. Fix it.

exercise_1.sql to run
SELECT title, genre
FROM games
WHERE critic_score = NULL;