← SQL roadmap

Topic 41 of 52 · Beginner

Why does a comparison silently exclude NULL rows?

NULL represents an unknown or missing value; comparisons such as NULL = 4 evaluate to UNKNOWN, not FALSE or TRUE. WHERE keeps only TRUE rows.

BeginnerFreshers · 0 yearsFundamentals

MySQL 8-compatible SELECT syntax; sample queries also execute in SQLite 3. Window functions require MySQL 8+.

Detailed answer

NULL represents an unknown or missing value; comparisons such as NULL = 4 evaluate to UNKNOWN, not FALSE or TRUE. WHERE keeps only TRUE rows.

Use IS NULL to test absence. If missing values should remain in an exclusion query, combine the comparison with OR column IS NULL. Decide explicitly whether missing data belongs in the result.

COALESCE supplies a fallback value, but replacing NULL with a sentinel can merge distinct business meanings. COUNT(column) omits missing values whereas COUNT(*) counts rows.

Internal working

SQL predicate evaluation uses three-valued logic. NOT UNKNOWN remains UNKNOWN; this explains why negating a comparison does not bring missing rows back.

Practical scenario

A placement eligibility report excludes students with missing city data when using city <> Chennai. Keep them for a separate data-cleanup workflow.

SELECT 1 WHERE NULL IS NULL;
SELECT COALESCE(NULL, 'missing') AS label;
Common mistakes
  • Writing column = NULL.
  • Assuming NOT IN is safe when its subquery can return NULL.
Key takeaways
  • WHERE retains only TRUE.
  • Test NULL with IS NULL.
  • Specify missing-data semantics.
Follow-up challenges
  • How does COUNT differ from COUNT(column)?
  • When would a NOT NULL constraint be preferable?
Practise SQL with the AI mentor

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.