How to Compare Two Values When One Is Null

In PostgreSQL, comparing two values when one or both can be NULL requires careful handling because NULL represents an unknown value. Standard equality checks like = and != will return NULL instead of TRUE or FALSE when NULL is involved.

1. Why NULL Is Tricky

In SQL, NULL means “unknown,” so expressions like NULL = NULL or NULL != value don’t behave as you might expect. Instead of TRUE or FALSE, the result is NULL—which behaves like FALSE in WHERE clauses.

2. Using COALESCE

COALESCE() is a useful function that replaces NULL with a default value. For example:

SELECT COALESCE(a, '') = COALESCE(b, '') FROM table_name;

This treats NULL as an empty string (or another default), making the comparison straightforward.

3. Using IS DISTINCT FROM

PostgreSQL offers the IS DISTINCT FROM operator, which treats NULL values as comparable:

SELECT a IS DISTINCT FROM b FROM table_name;

This returns TRUE if the values are different—including when one is NULL and the other is not.

To check for equality, you can use:

SELECT NOT (a IS DISTINCT FROM b) FROM table_name;

4. Using NULL-Safe Functions

You can also write a custom check:

SELECT (a = b) OR (a IS NULL AND b IS NULL) FROM table_name;

This explicitly handles both values being NULL as equality.

Summary

• Use COALESCE() to replace NULL with a default.
• Use IS DISTINCT FROM for a built-in, NULL-safe comparison.
• Use custom logic like (a = b) OR (a IS NULL AND b IS NULL) if you want full control.

By understanding how NULL works and using the right tools, you can safely compare values in PostgreSQL and avoid unexpected query results.