SQL Academy · Lesson

IS NULL, IS NOT NULL and COALESCE

Detect NULLs with IS NULL, replace them with COALESCE / NULLIF, and design queries that survive missing data gracefully.

Testing for NULL

Use IS NULL and IS NOT NULL — never = NULL:

SELECT * FROM users WHERE deleted_at IS NULL;
SELECT * FROM users WHERE deleted_at IS NOT NULL;

Comparing anything to NULL yields NULL (unknown). The row is dropped — but you don't get an error, so the bug ships:

SELECT * FROM users WHERE deleted_at = NULL;
-- → returns 0 rows, always