IS DISTINCT FROM for NULL-safe comparison in PostgreSQL - Step-by-Step Execution

Concept Flow - IS DISTINCT FROM for NULL-safe comparison

Compare two values

↓

Are both NULL?

Yes→Return FALSE (not distinct)

No↓

Are values equal?

Yes→Return FALSE (not distinct)

No↓

Return TRUE (distinct)

This flow checks if two values are different, treating NULLs as equal only if both are NULL.

Execution Sample

PostgreSQL

SELECT 1 IS DISTINCT FROM 1 AS result1,
       1 IS DISTINCT FROM 2 AS result2,
       NULL IS DISTINCT FROM NULL AS result3,
       NULL IS DISTINCT FROM 1 AS result4;

This query compares pairs of values using IS DISTINCT FROM to safely handle NULLs.

Execution Table

Step	Comparison	Values Compared	Are Both NULL?	Are Values Equal?	Result
1	1 IS DISTINCT FROM 1	1 and 1	No	Yes	FALSE
2	1 IS DISTINCT FROM 2	1 and 2	No	No	TRUE
3	NULL IS DISTINCT FROM NULL	NULL and NULL	Yes	N/A	FALSE
4	NULL IS DISTINCT FROM 1	NULL and 1	No	No	TRUE

💡 All comparisons evaluated; NULL-safe logic applied to determine distinctness.

Variable Tracker

Variable	Step 1	Step 2	Step 3	Step 4
Values Compared	1 and 1	1 and 2	NULL and NULL	NULL and 1
Are Both NULL?	No	No	Yes	No
Are Values Equal?	Yes	No	N/A	No
Result	FALSE	TRUE	FALSE	TRUE

Key Moments - 2 Insights

Why does NULL IS DISTINCT FROM NULL return FALSE instead of TRUE?

Because IS DISTINCT FROM treats two NULLs as equal, so they are not distinct. See execution_table row 3.

How is IS DISTINCT FROM different from the usual <> operator when NULLs are involved?

The usual <> returns NULL (unknown) if either side is NULL, but IS DISTINCT FROM returns TRUE or FALSE safely. See execution_table rows 3 and 4.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result of comparing 1 IS DISTINCT FROM 2?

A FALSE

B NULL

C TRUE

D Error

💡 Hint

Check execution_table row 2 under Result column.

At which step does the comparison find both values NULL?

A Step 3

B Step 2

C Step 1

D Step 4

💡 Hint

Look at the 'Are Both NULL?' column in execution_table.

If we replaced IS DISTINCT FROM with <> in step 4 (NULL <> 1), what would be the result?

A TRUE

B NULL

C FALSE

D Error

💡 Hint

Recall that <> returns NULL if either side is NULL, unlike IS DISTINCT FROM.

Concept Snapshot

IS DISTINCT FROM compares two values treating NULLs safely.
Returns TRUE if values differ or one is NULL and the other is not.
Returns FALSE if values are equal or both NULL.
Unlike <> operator, it never returns NULL.
Useful for NULL-safe equality checks in PostgreSQL.

Full Transcript

The IS DISTINCT FROM operator in PostgreSQL compares two values and returns TRUE if they are different, including when one is NULL and the other is not. It returns FALSE if the values are equal or both are NULL. This operator is NULL-safe, meaning it treats two NULLs as equal, unlike the standard <> operator which returns NULL when comparing with NULLs. The execution flow first checks if both values are NULL, then if they are equal, and finally returns TRUE if neither condition is met. This makes IS DISTINCT FROM very useful for comparisons where NULLs might appear and you want a clear TRUE or FALSE result.