MySQL IFNULL COALESCE: Conditional Functions Explained

These functions make a decision about a value inside a query. You met CASE already (CASE). These are the shorter tools for common cases.

query.sql

SQL

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    salary INT NOT NULL,
    bonus INT NULL,
    nickname VARCHAR(20) NULL,
    work_phone VARCHAR(20) NULL,
    mobile VARCHAR(20) NULL
);
INSERT INTO employees (name, salary, bonus, nickname, work_phone, mobile) VALUES
    ('Asha',   90000, 5000, 'Ash', NULL,    '98450'),
    ('Ben',    70000, NULL, NULL,  '2222',  NULL),
    ('Chitra', 60000, 0,    NULL,  NULL,    NULL);

IF(condition, then, else)

query.sql

SQL

SELECT name, salary, IF(salary >= 80000, 'senior band', 'standard band') AS band FROM employees ORDER BY id;

Output

OUTPUT

+--------+--------+---------------+
| name   | salary | band          |
+--------+--------+---------------+
| Asha   |  90000 | senior band   |
| Ben    |  70000 | standard band |
| Chitra |  60000 | standard band |
+--------+--------+---------------+
3 rows in set

IF is MySQL’s two-way choice. It is short, but not portable. CASE is the standard version.

IFNULL(value, replacement)

query.sql

SQL

SELECT name, bonus, IFNULL(bonus, 0) AS bonus_or_zero, salary + IFNULL(bonus, 0) AS total_pay FROM employees ORDER BY id;

Output

OUTPUT

+--------+-------+---------------+-----------+
| name   | bonus | bonus_or_zero | total_pay |
+--------+-------+---------------+-----------+
| Asha   |  5000 |          5000 |     95000 |
| Ben    |  NULL |             0 |     70000 |
| Chitra |     0 |             0 |     60000 |
+--------+-------+---------------+-----------+
3 rows in set

Without IFNULL, salary + bonus would be NULL for Ben. IFNULL is MySQL-specific, with exactly two arguments.

COALESCE: the first non-NULL

COALESCE(a, b, c, ...) returns the first argument that is not NULL. It is standard SQL, and the one to prefer:

query.sql

SQL

SELECT name, COALESCE(nickname, name) AS display_name, COALESCE(mobile, work_phone, 'no phone') AS best_phone FROM employees ORDER BY id;

Output

OUTPUT

+--------+--------------+------------+
| name   | display_name | best_phone |
+--------+--------------+------------+
| Asha   | Ash          | 98450      |
| Ben    | Ben          | 2222       |
| Chitra | Chitra       | no phone   |
+--------+--------------+------------+
3 rows in set

NULLIF(a, b)

NULLIF returns NULL when the two values are equal, otherwise the first value. It turns “magic values” into NULL, and prevents division by zero:

query.sql

SQL

SELECT name, bonus, NULLIF(bonus, 0) AS bonus_zero_is_unknown, salary / NULLIF(bonus, 0) AS ratio FROM employees ORDER BY id;

Output

OUTPUT

+--------+-------+-----------------------+---------+
| name   | bonus | bonus_zero_is_unknown | ratio   |
+--------+-------+-----------------------+---------+
| Asha   |  5000 |                  5000 | 18.0000 |
| Ben    |  NULL |                  NULL |    NULL |
| Chitra |     0 |                  NULL |    NULL |
+--------+-------+-----------------------+---------+
3 rows in set

Division by NULL is NULL, not an error. That is why / NULLIF(x, 0) is the standard guard.

The difference between zero, empty and NULL

query.sql

SQL

SELECT name, bonus, bonus IS NULL AS is_null, bonus = 0 AS is_zero, COALESCE(bonus, -1) AS shows_unknown_as_minus_one FROM employees ORDER BY id;

Output

OUTPUT

+--------+-------+---------+---------+----------------------------+
| name   | bonus | is_null | is_zero | shows_unknown_as_minus_one |
+--------+-------+---------+---------+----------------------------+
| Asha   |  5000 |       0 |       0 |                       5000 |
| Ben    |  NULL |       1 |    NULL |                         -1 |
| Chitra |     0 |       0 |       1 |                          0 |
+--------+-------+---------+---------+----------------------------+
3 rows in set

Chitra has a bonus of 0 (known, none). Ben’s bonus is NULL (unknown, not decided). Keep the two meanings apart, and use COALESCE only where it is right to treat unknown as a default.

Combining them

query.sql

SQL

SELECT name,
       COALESCE(nickname, name) AS shown_name,
       IF(bonus IS NULL, 'not decided', IF(bonus = 0, 'none', 'paid')) AS bonus_status
FROM employees ORDER BY id;

Output

OUTPUT

+--------+------------+--------------+
| name   | shown_name | bonus_status |
+--------+------------+--------------+
| Asha   | Ash        | paid         |
| Ben    | Ben        | not decided  |
| Chitra | Chitra     | none         |
+--------+------------+--------------+
3 rows in set

GREATEST and LEAST with NULL

GREATEST and LEAST return NULL when any argument is NULL. Wrap nullable arguments: GREATEST(COALESCE(a, 0), COALESCE(b, 0)).

Aggregates and NULL

SUM, AVG, MIN, MAX and COUNT(column) ignore NULL values. AVG(bonus) averages only the rows with a bonus. If “no bonus” should count as zero, say so: AVG(COALESCE(bonus, 0)).

query.sql

SQL

SELECT AVG(bonus) AS average_of_known, AVG(COALESCE(bonus, 0)) AS average_with_zero_for_null FROM employees;

Output

OUTPUT

+------------------+----------------------------+
| average_of_known | average_with_zero_for_null |
+------------------+----------------------------+
|        2500.0000 |                  1666.6667 |
+------------------+----------------------------+
1 row in set

Which one

Need	Use
Two-way choice	`IF()` or `CASE`
More than two outcomes	`CASE`
A default for `NULL`	`COALESCE` (portable) or `IFNULL`
Turn a value into `NULL`	`NULLIF`
First available value	`COALESCE`

lightbulb

Prefer the standard ones

COALESCE, NULLIF and CASE work on every database. IF and IFNULL are convenient but tie your SQL to MySQL and MariaDB.