NULLIF function behavior in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of NULLIF when values are equal?

Consider the query:

SELECT NULLIF(5, 5) AS result;

What is the value of result?

PostgreSQL

SELECT NULLIF(5, 5) AS result;

A Error

B 5

C 0

D NULL

💡 Hint

NULLIF returns NULL if both arguments are equal.

The NULLIF function returns NULL when the two arguments are equal. Since both are 5, the result is NULL.

❓ query_result

intermediate

What does NULLIF return when values differ?

What is the output of this query?

SELECT NULLIF('apple', 'orange') AS result;

PostgreSQL

SELECT NULLIF('apple', 'orange') AS result;

A 'apple'

B NULL

C 'orange'

D Error

💡 Hint

NULLIF returns the first argument if they are not equal.

Since 'apple' and 'orange' are different, NULLIF returns the first argument 'apple'.

🧠 Conceptual

advanced

How does NULLIF behave with NULL arguments?

What is the result of this query?

SELECT NULLIF(NULL, NULL) AS result;

PostgreSQL

SELECT NULLIF(NULL, NULL) AS result;

A TRUE

B Error

C NULL

D FALSE

💡 Hint

NULL compared to anything is unknown, but NULLIF treats NULL arguments specially.

NULLIF returns NULL if the two arguments are equal. However, NULL compared to NULL is unknown, so they are not considered equal. Therefore, NULLIF returns the first argument, which is NULL.

❓ query_result

advanced

What is the output of NULLIF with expressions?

Evaluate the query:

SELECT NULLIF(10/2, 5) AS result;

PostgreSQL

SELECT NULLIF(10/2, 5) AS result;

A 5

B NULL

C 2

D Error

💡 Hint

Calculate the first expression and compare to the second argument.

10 divided by 2 is 5, which equals the second argument. NULLIF returns NULL when both arguments are equal.

📝 Syntax

expert

Which NULLIF usage causes a syntax error?

Identify the option that will cause a syntax error in PostgreSQL:

A SELECT NULLIF(1);

B SELECT NULLIF('a', 'b');

C SELECT NULLIF(1, 1) FROM dual;

D SELECT NULLIF(NULL, 0);

💡 Hint

Check the number of arguments NULLIF requires.

NULLIF requires exactly two arguments. Option A provides only one argument, causing a syntax error.