MySQL + SQL · Lesson 62

IFNULL and COALESCE in MySQL

Q: Why do NULLs cause problems in calculations?

In SQL, any arithmetic with NULL results in NULL. So salary + bonus becomes NULL if bonus is NULL, hiding the salary. Using IFNULL or COALESCE to turn NULL into 0 fixes such calculations.

Q: How do I replace NULL with a default value?

Wrap the column in IFNULL or COALESCE with the default: IFNULL(phone, 'N/A') or COALESCE(phone, mobile, 'N/A'). The query then shows the default wherever the value would have been NULL.

In this lesson: Replace NULL with default values using IFNULL and COALESCE in MySQL — difference and examples with output. In Hindi & English.

Dealing with NULL

NULL means "no value" in SQL, and it can cause problems — empty displays and broken calculations. MySQL gives you IFNULL and COALESCE to replace NULL with a sensible default, so your results stay clean and your maths works.

💡 IFNULL and COALESCE swap NULL for a default value you choose.

IFNULL

SELECT name, IFNULL(phone, 'N/A') AS phone
FROM customers;

+------+---------+ | name | phone | +------+---------+ | Aman | 9999999 | | Diya | N/A | +------+---------+

IFNULL(value, default) returns the value if it is not null, otherwise the default. Here missing phone numbers show "N/A" instead of an empty NULL.

COALESCE

SELECT name, COALESCE(phone, mobile, 'N/A') AS contact
FROM customers;

COALESCE takes many values and returns the first non-null one. Here it tries phone first, then mobile, and finally "N/A". It is perfect for a chain of fallbacks.

IFNULL vs COALESCE

Point	IFNULL	COALESCE
Arguments	Exactly 2	2 or more
Standard SQL	MySQL-specific	Standard
Best for	One default	Chain of fallbacks

Use IFNULL for the simple two-value case, and COALESCE when you have several fallbacks or want standard SQL.

NULLs in calculations

-- bonus is NULL for some rows
SELECT name, salary + IFNULL(bonus, 0) AS total
FROM employees;

Any maths with NULL gives NULL, so salary + bonus would vanish when bonus is NULL. Wrapping bonus in IFNULL(bonus, 0) treats a missing bonus as 0, so the total is correct.

Which to use

IFNULL — quick, one column and one default.
COALESCE — several fallbacks, or when writing standard SQL.

Both solve the same core problem; COALESCE is just more flexible and portable across databases.

Common mistakes

Ignoring NULLs in maths. They turn the whole result into NULL.
Giving IFNULL too many arguments. It takes exactly two — use COALESCE for more.
Confusing NULL with 0 or empty string. NULL is "no value", not zero.
Comparing with = NULL. Use IS NULL, not = NULL.

Summary

NULL means "no value" and can break displays and calculations.
IFNULL(value, default) replaces NULL with a default (two arguments).
COALESCE returns the first non-null of several values.
Wrap columns in IFNULL/COALESCE to fix NULL maths.
Use IFNULL for one default, COALESCE for a chain of fallbacks.

Next, add conditional logic with the CASE expression, or round numbers with ROUND, CEIL and FLOOR.

Frequently Asked Questions

What does IFNULL do in MySQL?

IFNULL takes two arguments and returns the first if it is not null, otherwise the second. It is used to replace NULL with a default value, like showing 0 or "N/A" instead of an empty result.

What does COALESCE do?

COALESCE takes several arguments and returns the first one that is not null. It is like IFNULL but works with more than two values, so you can provide a chain of fallbacks and use the first available one.

What is the difference between IFNULL and COALESCE?

IFNULL takes exactly two arguments; COALESCE takes two or more. COALESCE is standard SQL and more flexible, while IFNULL is a MySQL-specific shortcut for the common two-argument case. For more than two options, use COALESCE.

Why do NULLs cause problems in calculations?

In SQL, any arithmetic with NULL results in NULL. So salary + bonus becomes NULL if bonus is NULL, hiding the salary. Using IFNULL or COALESCE to turn NULL into 0 fixes such calculations.

How do I replace NULL with a default value?

Wrap the column in IFNULL or COALESCE with the default: IFNULL(phone, 'N/A') or COALESCE(phone, mobile, 'N/A'). The query then shows the default wherever the value would have been NULL.

💻 Live Code Editor

This page's programs are ready here — run them, edit them, and learn. No installation needed.

Powered by OneCompiler. The code loads into the editor automatically — press Run to see the output. If the editor does not open, open it in a new tab.