IFNULL and COALESCE in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of IFNULL with NULL value

Consider a table employees with a column bonus that can be NULL. What is the output of this query?

SELECT IFNULL(bonus, 100) AS bonus_amount FROM employees WHERE id = 1;

Assuming bonus is NULL for id = 1.

MySQL

SELECT IFNULL(bonus, 100) AS bonus_amount FROM employees WHERE id = 1;

A 100

B 0

C NULL

D Error

💡 Hint

IFNULL returns the first argument if it is not NULL, otherwise the second.

Since the bonus is NULL, IFNULL returns the second argument, which is 100.

❓ query_result

intermediate

Output of COALESCE with multiple NULLs

Given a table orders with columns discount1, discount2, and discount3, all possibly NULL. What is the output of this query?

SELECT COALESCE(discount1, discount2, discount3, 0) AS discount FROM orders WHERE order_id = 5;

Assuming discount1 and discount2 are NULL, and discount3 is 15.

MySQL

SELECT COALESCE(discount1, discount2, discount3, 0) AS discount FROM orders WHERE order_id = 5;

A NULL

B 15

C 0

D Error

💡 Hint

COALESCE returns the first non-NULL value from its arguments.

Since discount1 and discount2 are NULL, COALESCE returns discount3 which is 15.

📝 Syntax

advanced

Identify the syntax error in COALESCE usage

Which option contains a syntax error when using COALESCE in MySQL?

A SELECT COALESCE();

B SELECT COALESCE(NULL, NULL, 0);

C SELECT COALESCE(NULL, 5, 10);

D SELECT COALESCE('a', 'b');

💡 Hint

COALESCE requires at least one argument.

Option A has no arguments inside COALESCE, which is invalid syntax.

❓ query_result

advanced

Difference between IFNULL and COALESCE output

Given the query:

SELECT IFNULL(NULL, 0) AS val1, COALESCE(NULL, 0) AS val2;

What is the output?

MySQL

SELECT IFNULL(NULL, 0) AS val1, COALESCE(NULL, 0) AS val2;

A val1 = NULL, val2 = 0

B val1 = NULL, val2 = NULL

C val1 = 0, val2 = NULL

D val1 = 0, val2 = 0

💡 Hint

Both IFNULL and COALESCE return the first non-NULL argument.

Both functions return 0 because the first argument is NULL and the second is 0.

🧠 Conceptual

expert

Behavior of COALESCE with subqueries returning NULL

Consider this query:

SELECT COALESCE((SELECT NULL), (SELECT NULL), 42) AS result;

What is the value of result?

MySQL

SELECT COALESCE((SELECT NULL), (SELECT NULL), 42) AS result;

A Error due to subqueries

B NULL

C 42

D Empty result set

💡 Hint

COALESCE returns the first non-NULL value among its arguments, including subquery results.

Both subqueries return NULL, so COALESCE returns the last argument 42.