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.

Practice

1. What does the NULLIF function do in MySQL?

easy

A. Returns the sum of two expressions

B. Returns NULL if two expressions are equal, otherwise returns the first expression

C. Returns the first non-NULL expression

D. Returns the second expression if it is NULL

Solution

1. Step 1: Understand the purpose of NULLIF

   NULLIF compares two expressions and returns NULL if they are equal.

2. Step 2: Identify the return value when expressions differ

   If the expressions are not equal, NULLIF returns the first expression.

3. Final Answer:

   Returns NULL if two expressions are equal, otherwise returns the first expression -> Option B

4. Quick Check:

   NULLIF returns NULL on equality [OK]

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

Common Mistakes:

• Confusing NULLIF with COALESCE
• Thinking it sums values
• Assuming it returns second expression

2. Which of the following is the correct syntax for the NULLIF function in MySQL?

easy

A. NULLIF(expression1 OR expression2)

B. NULLIF(expression1 = expression2)

C. NULLIF(expression1, expression2, expression3)

D. NULLIF(expression1, expression2)

Solution

1. Step 1: Recall the syntax format of NULLIF

   NULLIF takes exactly two expressions separated by a comma inside parentheses.

2. Step 2: Check each option's syntax

   NULLIF(expression1, expression2) matches the correct syntax: NULLIF(expression1, expression2). Others have wrong number of arguments or invalid operators.

3. Final Answer:

   NULLIF(expression1, expression2) -> Option D

4. Quick Check:

   NULLIF syntax uses two expressions separated by comma [OK]

Hint: NULLIF always uses two expressions separated by a comma [OK]

Common Mistakes:

• Using comparison operator inside NULLIF
• Providing more than two arguments
• Using logical operators inside NULLIF

3. Given the table sales with columns id and amount, what will this query return?

SELECT id, NULLIF(amount, 0) AS adjusted_amount FROM sales;

If the table data is:
id | amount
1 | 100
2 | 0
3 | 50

medium

A. [{id:1, adjusted_amount:100}, {id:2, adjusted_amount:0}, {id:3, adjusted_amount:50}]

B. [{id:1, adjusted_amount:NULL}, {id:2, adjusted_amount:0}, {id:3, adjusted_amount:NULL}]

C. [{id:1, adjusted_amount:100}, {id:2, adjusted_amount:NULL}, {id:3, adjusted_amount:50}]

D. [{id:1, adjusted_amount:NULL}, {id:2, adjusted_amount:NULL}, {id:3, adjusted_amount:NULL}]

Solution

1. Step 1: Understand NULLIF(amount, 0)

   NULLIF returns NULL if amount equals 0, else returns amount.

2. Step 2: Apply to each row

   Row 1: amount=100, not 0, returns 100.
   Row 2: amount=0, equals 0, returns NULL.
   Row 3: amount=50, not 0, returns 50.

3. Final Answer:

   [{id:1, adjusted_amount:100}, {id:2, adjusted_amount:NULL}, {id:3, adjusted_amount:50}] -> Option C

4. Quick Check:

   NULLIF returns NULL only when amount is zero [OK]

Hint: NULLIF returns NULL when first value equals second [OK]

Common Mistakes:

• Assuming NULLIF returns 0 instead of NULL
• Confusing order of arguments
• Thinking NULLIF changes non-zero values

4. Identify the error in this query:

SELECT id, NULLIF(amount) FROM sales;

medium

A. NULLIF requires exactly two arguments

B. NULLIF cannot be used in SELECT clause

C. amount column does not exist

D. NULLIF should be written as NULLIF(amount = 0)

Solution

1. Step 1: Check NULLIF function usage

   NULLIF requires two arguments: expression1 and expression2.

2. Step 2: Analyze the query

   The query uses NULLIF with only one argument, which is invalid syntax.

3. Final Answer:

   NULLIF requires exactly two arguments -> Option A

4. Quick Check:

   NULLIF needs two expressions separated by comma [OK]

Hint: NULLIF always needs two arguments [OK]

Common Mistakes:

• Using NULLIF with one argument
• Writing NULLIF with comparison inside
• Assuming NULLIF works like ISNULL

5. You want to replace zero values in the score column with NULL in a query result. Which query correctly uses NULLIF to achieve this?

hard

A. SELECT id, NULLIF(score, 0) AS score_or_null FROM results;

B. SELECT id, NULLIF(0, score) AS score_or_null FROM results;

C. SELECT id, NULLIF(score) AS score_or_null FROM results;

D. SELECT id, NULLIF(score = 0) AS score_or_null FROM results;

Solution

1. Step 1: Understand the goal

   We want to convert zero scores to NULL, keep others unchanged.

2. Step 2: Apply NULLIF correctly

   NULLIF(score, 0) returns NULL if score is 0, else returns score.
   SELECT id, NULLIF(score, 0) AS score_or_null FROM results; matches this logic.

3. Step 3: Check other options

   SELECT id, NULLIF(0, score) AS score_or_null FROM results; reverses arguments, changing logic.
   SELECT id, NULLIF(score) AS score_or_null FROM results; has missing argument.
   SELECT id, NULLIF(score = 0) AS score_or_null FROM results; uses invalid syntax.

4. Final Answer:

   SELECT id, NULLIF(score, 0) AS score_or_null FROM results; -> Option A

5. Quick Check:

   NULLIF(score, 0) replaces zeros with NULL [OK]

Hint: Use NULLIF(column, 0) to convert zeros to NULL [OK]

Common Mistakes:

• Swapping argument order
• Using only one argument
• Using comparison inside NULLIF