IS NULL & IS NOT NULL

Data is often incomplete. In player_profiles some players have no nickname, and David Warner has no team. In innings, one of MS Dhoni's scores is missing. NULL marks these unknown values — and it needs its own operator.

Definition

NULL means "no value / unknown". It is not zero, not an empty string '', and not the text 'NULL'.

IS NULL returns TRUE when a value is NULL; IS NOT NULL returns TRUE when a value is present. Unlike every other comparison, they never return UNKNOWN — they always give a clear TRUE or FALSE.

Why a special operator? Any normal comparison with NULL (=, <>, >, IN…) asks "is unknown equal to X?" — and the honest answer is UNKNOWN. WHERE drops UNKNOWN rows, so WHERE team = NULL returns nothing, ever.

Three-valued logic

Syntax

SELECT column_list
FROM table_name
WHERE column_name IS NULL;

SELECT column_list
FROM table_name
WHERE column_name IS NOT NULL;

Example — players with no team

SELECT name, team
FROM player_profiles
WHERE team IS NULL;

Compare with WHERE team = NULL, which returns 0 rows.

Example — players who do have a team

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

Example — NULL hides from other filters

The innings table has 12 rows. MS Dhoni's 31 March score is NULL.

SELECT player, runs
FROM innings
WHERE runs <> 0;

This returns 10 rows, not 11: Rohit's 0 is removed (correct), but Dhoni's NULL innings is also removed, because NULL <> 0 is UNKNOWN. To keep it:

SELECT player, runs
FROM innings
WHERE runs <> 0
   OR runs IS NULL;   -- 11 rows

Common mistakes

• ❌ WHERE team = NULL / WHERE team <> NULL → always 0 rows. ✅ IS NULL / IS NOT NULL.
• ❌ WHERE team = 'NULL' → searches for the 4-letter text "NULL". ✅ IS NULL.
• ❌ Treating NULL as 0 or ''. An empty string is a real (known) value; NULL is unknown.
• ❌ WHERE team IS NOT 'MI' → invalid; IS only works with NULL. ✅ team <> 'MI'.

Note: SQL Server has a legacy setting SET ANSI_NULLS OFF that makes = NULL work. It is deprecated — always write IS NULL. To replace NULLs in the output, use ISNULL(col, value) or COALESCE(col, value) (covered in the functions chapter).

Interview tip

Q: Why doesn't WHERE col = NULL work? A: NULL means unknown, so comparing anything to it with = yields UNKNOWN, not TRUE. WHERE keeps only TRUE rows. IS NULL is the only correct test.