IS DISTINCT FROM for NULL-safe comparison in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does IS DISTINCT FROM do in PostgreSQL?

IS DISTINCT FROM compares two values and returns true if they are different, including when one is NULL and the other is not. It treats NULL as a comparable value.

beginner

How is IS DISTINCT FROM different from the usual = operator when comparing NULL values?

The usual = operator returns NULL (unknown) when comparing NULL to anything, even NULL. IS DISTINCT FROM returns false if both values are NULL, and true if only one is NULL.

beginner

Write a simple SQL query using IS DISTINCT FROM to find rows where column a differs from column b, including NULL differences.

SELECT * FROM table_name WHERE a IS DISTINCT FROM b;

beginner

Why is IS DISTINCT FROM useful in real-life database queries?

It helps to compare values safely when NULL values are involved, avoiding unexpected NULL results and making logic clearer and more reliable.

beginner

What will the expression NULL IS DISTINCT FROM NULL return?

It returns false because both sides are NULL, so they are not distinct.

What does a IS DISTINCT FROM b return if a = NULL and b = 5?

A true

B false

C NULL

D Error

Since one value is NULL and the other is 5, they are distinct, so it returns true.

What is the result of 5 IS DISTINCT FROM 5?

A false

B true

C NULL

D Error

Both values are equal and not NULL, so it returns false.

Which operator treats NULL as a comparable value?

A =

B IS DISTINCT FROM

C <>

D IS NULL

IS DISTINCT FROM treats NULL as a comparable value, unlike = or <>.

If both values are NULL, what does a IS DISTINCT FROM b return?

A NULL

B true

C false

D Error

It returns false because both NULL values are considered not distinct.

Why might you prefer IS DISTINCT FROM over = in queries?

A It only works with numbers

B It is faster

C It returns <code>NULL</code> for all comparisons

D It handles <code>NULL</code> comparisons safely

IS DISTINCT FROM safely handles NULL comparisons, avoiding unknown results.

Explain how IS DISTINCT FROM works when comparing two values, especially with NULLs.

Think about how normal = operator treats NULL and how this is different.

Describe a situation where using IS DISTINCT FROM is better than using = in a SQL query.

Consider comparing two columns where some values might be missing.