NULLIF function behavior in PostgreSQL - Step-by-Step Execution

Concept Flow - NULLIF function behavior

Start NULLIF(a, b)

↓

Compare a and b

↓

Return NULL

↓

End

NULLIF compares two values; if they are equal, it returns NULL, otherwise it returns the first value.

Execution Sample

PostgreSQL

SELECT NULLIF(5, 5) AS result1, NULLIF(5, 3) AS result2;

This query returns NULL for the first expression because 5 equals 5, and returns 5 for the second because 5 does not equal 3.

Execution Table

Step	Input a	Input b	Condition (a = b)?	Output
1	5	5	True	NULL
2	5	3	False	5
3	NULL	NULL	UNKNOWN	NULL
4	NULL	5	UNKNOWN	NULL

💡 All inputs processed; NULLIF returns NULL if inputs are equal, else returns first input.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4
a	5	5	5	NULL	NULL
b	5	5	3	NULL	5
Output	N/A	NULL	5	NULL	NULL

Key Moments - 2 Insights

Why does NULLIF return NULL when both inputs are NULL?

Because NULL = NULL evaluates to UNKNOWN (not TRUE) in SQL, NULLIF returns the first input (NULL), as shown in execution_table row 3.

What happens if the first input is NULL but the second is not?

NULLIF returns the first input (NULL) because the inputs are not equal, as shown in execution_table row 4.

Visual Quiz - 3 Questions

Look at the execution_table, what is the output when a=5 and b=5?

A Error

B NULL

C 5

D 0

💡 Hint

Check row 1 in the execution_table where a=5 and b=5.

At which step does NULLIF return the first input value instead of NULL?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look for the step where condition (a = b) is False and output equals a.

If input b changes from 3 to 5 in step 2, what would be the output?

A NULL

B 5

C Error

D 3

💡 Hint

Refer to the condition check in execution_table and how output depends on equality.

Concept Snapshot

NULLIF(a, b) returns NULL if a equals b.
Otherwise, it returns a.
If both inputs are NULL, it returns NULL.
Useful to avoid division by zero or replace equal values with NULL.

Full Transcript

The NULLIF function compares two inputs. If they are equal, it returns NULL. If not, it returns the first input. For example, NULLIF(5,5) returns NULL because both inputs are equal. NULLIF(5,3) returns 5 because inputs differ. When both inputs are NULL, NULLIF returns NULL. If the first input is NULL and the second is not, it returns NULL because inputs are not equal. This behavior helps handle special cases like avoiding errors or marking equal values as NULL.