IFNULL and COALESCE in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the IFNULL function do in MySQL?

IFNULL(expression, alternative) returns the alternative value if the expression is NULL; otherwise, it returns the expression's value.

intermediate

How is COALESCE different from IFNULL?

COALESCE(expression1, expression2, ..., expressionN) returns the first non-NULL expression from the list, while IFNULL only takes two arguments.

beginner

Write a simple example using IFNULL to replace NULL with 0.

SELECT IFNULL(salary, 0) AS salary FROM employees;

beginner

Write a simple example using COALESCE to find the first non-NULL value among three columns.

SELECT COALESCE(phone_home, phone_mobile, phone_work) AS contact_phone FROM contacts;

intermediate

Why might you prefer COALESCE over IFNULL in some cases?

Because COALESCE can check multiple values and return the first non-NULL, it is more flexible for handling several possible NULLs.

What will IFNULL(NULL, 'default') return?

A 'default'

B NULL

C Error

D Empty string

IFNULL returns the second argument if the first is NULL.

Which function can take more than two arguments to check for the first non-NULL value?

A COALESCE

B IFNULL

C ISNULL

D NVL

COALESCE accepts multiple arguments and returns the first non-NULL.

What does COALESCE(NULL, NULL, 'apple', 'banana') return?

A NULL

B 'apple'

C 'banana'

D Error

COALESCE returns the first non-NULL value, which is 'apple'.

If salary is 5000, what does IFNULL(salary, 0) return?

A Error

B NULL

C 0

D 5000

IFNULL returns the original value if it is not NULL.

Which function is standard SQL and works in many databases?

A NVL

B IFNULL

C COALESCE

D ISNULL

COALESCE is part of the SQL standard and widely supported.

Explain how IFNULL works and give a simple example.

Think about replacing missing values with something else.

Describe the difference between IFNULL and COALESCE and when to use each.

Consider how many values you want to check for NULL.