Understanding NULL: IS NULL, IS NOT NULL, Empty vs Blank

David Warner was not picked by any team in our data — his team is NULL. NULL is the most misunderstood value in SQL: it is not 0, not an empty string, and it doesn't even equal itself. Master it here and you will avoid a whole class of wrong reports.

Definition

NULL is a special marker meaning "no value" — the value is unknown or missing. It is not a number, not text and not zero; it is the absence of a value.

SQL uses three-valued logic: a condition can be TRUE, FALSE or UNKNOWN. WHERE keeps only rows where the condition is TRUE, so UNKNOWN rows are silently dropped.

Where do NULLs come from?

The NULL toolkit

The replace/create functions have their own topic next; this topic covers the checks and NULL behaviour.

Syntax

SELECT columns FROM table_name WHERE column_name IS NULL;
SELECT columns FROM table_name WHERE column_name IS NOT NULL;

IS NULL returns TRUE when the value is missing. IS NOT NULL returns TRUE when the value exists. They are the only correct way to test for NULL.

Example — IS NULL: find the missing

SELECT name, team
FROM player_profiles
WHERE team IS NULL;

Example — IS NOT NULL: keep the known

SELECT name, team
FROM player_profiles
WHERE team IS NOT NULL;

Warner is crossed out because he has no value. (player_profiles is our copy of the PDF's chapter-7 players table, with the NULL team for Warner.)

The = NULL trap

WHERE team = NULL returns no rows at all, because team = NULL is UNKNOWN for every row. Even worse, WHERE team <> 'MI' silently drops Warner too:

SELECT name, team
FROM player_profiles
WHERE team <> 'MI';

Warner's NULL <> 'MI' is UNKNOWN, not TRUE. To include him: WHERE team <> 'MI' OR team IS NULL.

JOINs & NULL

NULL never equals NULL, so NULL keys do not match in a join. Suppose table A has (Kohli, RCB) and (Warner, NULL), and table B has (RCB, 'Royal Challengers') and (NULL, 'Unassigned pool').

Plain join result:

NULL-safe join result:

COALESCE turns both NULLs into the same placeholder '?', and '?' = '?' is TRUE.

NULL vs empty vs blank

Three things that look the same on screen — but are not:

Clean your data before analysing it, so all three become one consistent value:

NULLIF(TRIM(team), '')                         -- blanks and empty text become NULL
COALESCE(NULLIF(TRIM(team), ''), 'Unsold')     -- then replace with a default

TRIM turns ' ' into ''; NULLIF(…, '') turns '' into NULL; COALESCE replaces NULL with 'Unsold'.

Common mistakes

• ❌ WHERE team = NULL → ✅ WHERE team IS NULL.
• ❌ WHERE team <> 'MI' expecting NULL rows too → ✅ add OR team IS NULL.
• ❌ Treating NULL runs as 0 in averages without thinking — AVG ignores NULLs; replacing them with 0 changes the answer.
• ❌ Checking only IS NULL on imported text — empty '' and blank ' ' values slip through. ✅ NULLIF(TRIM(col), '') IS NULL.

Interview tip

Q: Why does SELECT * FROM players WHERE team NOT IN ('MI', NULL) return no rows? NOT IN expands to team <> 'MI' AND team <> NULL. The second part is always UNKNOWN, so the whole condition is never TRUE. Avoid NULLs inside NOT IN lists (or use NOT EXISTS).