How to Use IFNULL in MySQL: Syntax and Examples

In MySQL, IFNULL(expr1, expr2) returns expr2 if expr1 is NULL; otherwise, it returns expr1. This function helps replace NULL values with a default value in query results.

Syntax

The IFNULL function takes two arguments:

• expr1: The expression to check for NULL.
• expr2: The value to return if expr1 is NULL.

If expr1 is not NULL, IFNULL returns expr1. Otherwise, it returns expr2.

sql

IFNULL(expr1, expr2)

Example

This example shows how to use IFNULL to replace NULL values in a table of employees with their bonus amounts. If the bonus is NULL, it will show 0 instead.

sql

CREATE TABLE employees (
  id INT,
  name VARCHAR(20),
  bonus DECIMAL(10,2)
);

INSERT INTO employees VALUES
(1, 'Alice', 100.00),
(2, 'Bob', NULL),
(3, 'Charlie', 50.00);

SELECT name, IFNULL(bonus, 0) AS bonus_amount FROM employees;

Output

name    | bonus_amount
--------|--------------
Alice   | 100.00
Bob     | 0.00
Charlie | 50.00

Common Pitfalls

One common mistake is confusing IFNULL with COALESCE. IFNULL only takes two arguments, while COALESCE can take many. Also, IFNULL only checks the first expression for NULL.

Another pitfall is using IFNULL on columns that are not nullable, which is unnecessary.

sql

/* Wrong: Using IFNULL with more than two arguments (syntax error) */
-- SELECT IFNULL(col1, col2, col3) FROM table;

/* Right: Use COALESCE for multiple values */
SELECT COALESCE(col1, col2, col3) FROM table;

Quick Reference

Function	Description	Example
IFNULL(expr1, expr2)	Returns expr2 if expr1 is NULL, else expr1	IFNULL(NULL, 'default') returns 'default'
COALESCE(expr1, expr2, ...)	Returns first non-NULL expression	COALESCE(NULL, NULL, 'yes') returns 'yes'

Key Takeaways

Use IFNULL(expr1, expr2) to replace NULL values with a default in MySQL queries.

IFNULL only accepts two arguments and returns expr2 if expr1 is NULL.

For multiple values, use COALESCE instead of IFNULL.

Applying IFNULL on non-nullable columns is unnecessary.

IFNULL helps avoid NULL results in query outputs for better readability.