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.

Practice

1. What does the NULLIF function return when both its arguments are equal?

easy

A. It returns an error.

B. It returns the second argument.

C. It returns the first argument.

D. It returns NULL.

Solution

1. Step 1: Understand the purpose of NULLIF

   The NULLIF function compares two values and returns NULL if they are equal.

2. Step 2: Apply the function behavior

   If both arguments are the same, NULLIF returns NULL instead of any value.

3. Final Answer:

   It returns NULL. -> Option D

4. Quick Check:

   Equal values = NULL [OK]

Hint: NULLIF returns NULL only when arguments match exactly [OK]

Common Mistakes:

• Thinking it returns the second argument when equal
• Assuming it always returns the first argument
• Believing it throws an error on equal values

2. Which of the following is the correct syntax to use NULLIF in PostgreSQL?

easy

A. NULLIF(value1; value2)

B. NULLIF value1, value2

C. NULLIF(value1, value2)

D. NULLIF[value1, value2]

Solution

1. Step 1: Recall the function syntax

   NULLIF is a standard SQL function that takes two arguments inside parentheses separated by a comma.

2. Step 2: Identify the correct syntax

   Only NULLIF(value1, value2) uses parentheses and comma correctly.

3. Final Answer:

   NULLIF(value1, value2) -> Option C

4. Quick Check:

   Function syntax = parentheses + comma [OK]

Hint: Use parentheses and comma for SQL functions [OK]

Common Mistakes:

• Omitting parentheses
• Using semicolon instead of comma
• Using square brackets instead of parentheses

3. What is the result of this query?

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

medium

A. result1 = NULL, result2 = 7

B. result1 = 5, result2 = 7

C. result1 = NULL, result2 = NULL

D. result1 = 5, result2 = NULL

Solution

1. Step 1: Evaluate NULLIF(5, 5)

   Since both arguments are equal, NULLIF(5, 5) returns NULL.

2. Step 2: Evaluate NULLIF(7, 5)

   Arguments differ, so NULLIF(7, 5) returns the first argument, 7.

3. Final Answer:

   result1 = NULL, result2 = 7 -> Option A

4. Quick Check:

   Equal args = NULL, different args = first value [OK]

Hint: NULLIF returns NULL if equal, else first value [OK]

Common Mistakes:

• Assuming NULLIF always returns first argument
• Confusing which argument is returned when different
• Expecting both results to be NULL

4. Identify the error in this query:

SELECT NULLIF(10 10) AS result;

medium

A. Missing comma between arguments

B. NULLIF cannot take numeric arguments

C. NULLIF requires three arguments

D. Using wrong function name

Solution

1. Step 1: Check function argument syntax

   Arguments in NULLIF must be separated by a comma.

2. Step 2: Identify the missing comma

   The query has NULLIF(10 10) missing a comma between 10 and 10.

3. Final Answer:

   Missing comma between arguments -> Option A

4. Quick Check:

   Arguments need comma separator [OK]

Hint: Check commas between function arguments [OK]

Common Mistakes:

• Forgetting commas between arguments
• Thinking NULLIF needs more than two arguments
• Assuming numeric arguments are invalid

5. You want to avoid division by zero errors in this query:

SELECT value / divisor FROM numbers;

How can you use NULLIF to prevent errors when divisor is zero?

hard

A. SELECT NULLIF(value, 0) / divisor FROM numbers;

B. SELECT value / NULLIF(divisor, 0) FROM numbers;

C. SELECT value / divisor WHERE divisor != 0;

D. SELECT value / divisor NULLIF divisor, 0 FROM numbers;

Solution

1. Step 1: Understand division by zero problem

   Dividing by zero causes an error. We want to replace zero divisor with NULL to avoid error.

2. Step 2: Use NULLIF to replace zero divisor

   NULLIF(divisor, 0) returns NULL if divisor is zero, preventing division by zero error.

3. Step 3: Check options

   SELECT value / NULLIF(divisor, 0) FROM numbers; correctly uses value / NULLIF(divisor, 0). SELECT NULLIF(value, 0) / divisor FROM numbers; replaces value, not divisor. SELECT value / divisor WHERE divisor != 0; filters rows but does not use NULLIF. SELECT value / divisor NULLIF divisor, 0 FROM numbers; has syntax error.

4. Final Answer:

   SELECT value / NULLIF(divisor, 0) FROM numbers; -> Option B

5. Quick Check:

   Replace zero divisor with NULL using NULLIF [OK]

Hint: Use NULLIF(divisor, 0) to avoid division by zero [OK]

Common Mistakes:

• Replacing numerator instead of divisor
• Filtering rows instead of using NULLIF
• Writing invalid syntax with NULLIF