NULL comparison: unexpected query results
Quick summary
Your WHERE clause uses = or != to compare with NULL — NULL comparisons always return unknown, not true or false.
Why this happens
A WHERE clause uses column = NULL instead of column IS NULL to check for null values
A comparison with a potentially NULL column excludes rows where the column is NULL
An outer join produces NULL values that are then compared with = in a subsequent filter
Minimal example
await db.query('SELECT * FROM users WHERE deleted_at = NULL');
// Returns 0 rows — NULL = NULL is unknown, not trueawait db.query('SELECT * FROM users WHERE deleted_at IS NULL');
// Returns all users where deleted_at is NULL (not deleted)Using IS NULL instead of = NULL correctly tests for NULL values because IS NULL evaluates to true for NULL, while = NULL evaluates to unknown.
How to diagnose
Test the query with NULL values in the data and check if expected rows are missing
Review WHERE clauses for = NULL or != NULL patterns that should be IS NULL or IS NOT NULL
Use COALESCE or NVL to provide a default value for NULL columns before comparison
How to fix
Use IS NULL and IS NOT NULL instead of = NULL and != NULL
Use COALESCE(column, default) to replace NULL with a default value before comparison
Use the NULL-safe equality operator (<=> in MySQL or IS NOT DISTINCT FROM in PostgreSQL)
How to prevent
Always use IS NULL and IS NOT NULL for NULL checks — never use = or != with NULL
Be aware of nullable columns in joins and filters and handle NULL cases explicitly
Related Resources
Related Glossary
Related Lessons
- NULL Handling
Learn about NULL.