IS NULL

In SQL, NULL represents missing or unknown information. It is not a value — it is the absence of a value. Because of this, you cannot compare NULL using the standard equality operator (=). Instead, PostgreSQL provides the IS NULL and IS NOT NULL operators specifically for NULL checks.

Why = NULL does not work

Any comparison involving NULL evaluates to NULL (not true or false):

SELECT null = null AS result;

result
--------
 null
(1 row)

This means WHERE column = NULL never returns any rows — the condition is always NULL, which is treated as false by the WHERE clause. Always use IS NULL instead.

IS NULL and IS NOT NULL syntax

value IS NULL       -- returns true if value is NULL
value IS NOT NULL   -- returns true if value is not NULL

PostgreSQL IS NULL examples

Find addresses where address2 is NULL:

SELECT address, address2
FROM address
WHERE address2 IS NULL;

address              | address2
----------------------+----------
 47 MySakila Drive    | null
 28 MySQL Boulevard   | null
 23 Workhaven Lane    | null
 1411 Lillydale Drive | null
(4 rows)

Find addresses where address2 is not NULL:

SELECT address, address2
FROM address
WHERE address2 IS NOT NULL;

address                | address2
-----------------------+----------
 1913 Hanoi Way        |
 1121 Loja Avenue      |
 692 Joliet Street     |
 ...

Note: the address2 values in this result are empty strings, not NULL. This illustrates a common data quality issue where empty strings and NULL are mixed in the same column.

NULL handling tips

• NULL is not equal to NULL: two NULL values in a JOIN condition do not match each other. Use IS NOT DISTINCT FROM if you want NULL = NULL behavior in comparisons.
• NOT IN with NULLs: if a list used with NOT IN contains any NULL, the entire expression evaluates to NULL and returns no rows. This is a common bug — use NOT EXISTS as a safer alternative.
• Aggregate functions ignore NULLs: COUNT(column) counts only non-NULL values. SUM, AVG, MIN, MAX all skip NULLs automatically.
• ORDER BY and NULLs: by default, NULLs sort last in ascending order and first in descending order. Use NULLS FIRST or NULLS LAST to control this explicitly.
• Useful NULL functions: COALESCE(value, default) returns the first non-NULL argument; NULLIF(a, b) returns NULL if a equals b; IS DISTINCT FROM treats NULL as a comparable value.

Reference: PostgreSQL documentation — Comparison Functions and Operators.