NULLIF function in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this NULLIF query?

Consider the following query:

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

What will be the output row?

MySQL

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

A {"result1": 5, "result2": null}

B {"result1": 0, "result2": 5}

C {"result1": null, "result2": null}

D {"result1": null, "result2": 5}

💡 Hint

NULLIF returns NULL if the two arguments are equal, otherwise it returns the first argument.

NULLIF(5, 5) returns NULL because both arguments are equal. NULLIF(5, 3) returns 5 because the arguments differ.

🧠 Conceptual

intermediate

What does NULLIF(x, y) do in SQL?

Choose the correct description of the NULLIF function behavior.

A Always returns NULL regardless of inputs.

B Returns y if x equals y; otherwise returns NULL.

C Returns NULL if x equals y; otherwise returns x.

D Returns x if x equals y; otherwise returns y.

💡 Hint

Think about what happens when the two inputs are the same.

NULLIF compares two expressions and returns NULL if they are equal; otherwise, it returns the first expression.

📝 Syntax

advanced

Which query uses NULLIF correctly to avoid division by zero?

You want to calculate the ratio of column a to column b but avoid division by zero errors. Which query is correct?

MySQL

SELECT a / NULLIF(b, 0) AS ratio FROM table1;

A SELECT a / b NULLIF(0) AS ratio FROM table1;

B SELECT a / NULLIF(b, 0) AS ratio FROM table1;

C SELECT NULLIF(a, 0) / b AS ratio FROM table1;

D SELECT a / NULLIF(0, b) AS ratio FROM table1;

💡 Hint

NULLIF should replace zero divisor with NULL to prevent error.

NULLIF(b, 0) returns NULL if b is zero, so division by NULL returns NULL instead of error.

❓ optimization

advanced

How does NULLIF improve query performance in conditional expressions?

Given a large table, which use of NULLIF can optimize filtering rows where a column equals a specific value?

A Using NULLIF(column, value) IS NULL to filter rows equal to value.

B Using column = NULLIF(column, value) to filter rows equal to value.

C Using NULLIF(column, value) = value to filter rows equal to value.

D Using NULLIF(column, value) != NULL to filter rows equal to value.

💡 Hint

Think about how NULLIF returns NULL when values match.

NULLIF(column, value) returns NULL if column equals value, so checking IS NULL filters those rows efficiently.

🔧 Debug

expert

Why does this query raise a division by zero error despite using NULLIF?

Query:

SELECT a / NULLIF(0, b) AS ratio FROM table1;

Why does it still cause a division by zero error?

A Because NULLIF arguments are reversed; it should be NULLIF(b, 0).

B Because NULLIF(0, b) returns NULL when b is zero, which causes division by zero.

C Because NULLIF(0, b) returns 0 when b is not zero, causing division by zero.

D Because division by NULL is not allowed in MySQL.

💡 Hint

Check the order of arguments in NULLIF carefully.

NULLIF(0, b) returns NULL only if 0 equals b, which is false unless b=0. The correct usage is NULLIF(b, 0) to replace zero divisor with NULL.