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