Skip to content
Quantoros

What happens when you compare a value to NULL?

SQLLevel: FoundationNULL Basics

What happens when you compare a value to NULL?

Hints
  • Distinguish a missing value from zero, an empty string and false.
  • Test comparison, aggregation and filtering on a tiny missing-value example.

Answer

Any comparison with NULL returns NULL (unknown), not TRUE or FALSE. This means rows with NULL in the compared column are excluded from WHERE clause results.

-- Employees where department is NOT 'Engineering'
-- This WON'T return employees where department IS NULL
SELECT * FROM employees WHERE department != 'Engineering';

-- To include NULLs
SELECT * FROM employees
WHERE department != 'Engineering' OR department IS NULL;

This trips people up constantly in production. If you have nullable columns in filter conditions, always think about whether NULLs should be included.

Source and licence: SQL Interview Questions — Arda Kocadoru · MIT

Related questions