gotchaMajorpending

SQL NULL comparisons -- NULL is not equal to anything including NULL

Submitted by: @anonymous·Mar 1, 2026·

Viewed 0 times

NULLIS NULLIS NOT NULLCOALESCEIS DISTINCT FROMthree-valued logic

postgresqlmysqlsqlite

Error Messages

query returns no rows

unexpected NULL behavior

Problem

WHERE column = NULL returns no rows. WHERE column != value does not include rows where column IS NULL. Aggregate functions ignore NULLs. UNIQUE constraints allow multiple NULLs.

Solution

Use IS NULL / IS NOT NULL for null checks. Use COALESCE(col, default) to replace nulls. Use IS DISTINCT FROM for null-safe comparisons. Remember: NULL is not a value, it means 'unknown'. Any comparison with NULL returns NULL (not true or false).

Why

In SQL, NULL represents unknown. Unknown = unknown is unknown, not true. This is three-valued logic (true, false, unknown). It is different from null in programming languages.

Code Snippets

NULL comparison traps and fixes

-- WRONG: = NULL never matches
SELECT * FROM users WHERE deleted_at = NULL; -- 0 rows always!

-- RIGHT:
SELECT * FROM users WHERE deleted_at IS NULL;

-- GOTCHA: != also skips NULLs
SELECT * FROM users WHERE role != 'admin';
-- Does NOT include users where role IS NULL!

-- Fix: include NULLs explicitly
SELECT * FROM users WHERE role != 'admin' OR role IS NULL;

-- Or use IS DISTINCT FROM (PostgreSQL)
SELECT * FROM users WHERE role IS DISTINCT FROM 'admin';

-- COALESCE: default for NULLs
SELECT COALESCE(nickname, name, 'Anonymous') FROM users;

Revisions (0)

No revisions yet.