Skip to content

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

✗ Broken code
await db.query('SELECT * FROM users WHERE deleted_at = NULL');
// Returns 0 rows — NULL = NULL is unknown, not true
✓ Fixed code
await 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

← Back to language