SQL IFNULL, COALESCE and NULLIF

NULL breaks arithmetic, string concatenation and averages (NULL Values). Three small functions fix that: COALESCE and IFNULL replace NULL with a value you choose, and NULLIF does the opposite, turning a value into NULL, which is the standard trick to avoid division by zero.

Sample data

COALESCE

COALESCE(a, b, c, ...) returns the first argument that is not NULL:

SELECT first_name, commission,       COALESCE(commission, 0)          AS commission_or_zero,       salary + COALESCE(commission, 0) AS total_payFROM employeesWHERE dept_id IN (2, 3);

With several arguments it picks the first available value. Show the best contact for each customer: email, otherwise city:

SELECT customer_name, email, city,       COALESCE(email, CONCAT('no email - ', city)) AS contactFROM customersWHERE customer_id IN (3, 4, 5);

IFNULL

IFNULL(a, b) is MySQL’s two-argument version: a if it is not NULL, otherwise b.

SELECT first_name,       IFNULL(dept_id, 'none')    AS dept,       IFNULL(manager_id, 'top')  AS reports_toFROM employeesWHERE emp_id IN (100, 101, 102);

Prefer COALESCE unless you are sure the query will only ever run on MySQL.

NULLIF: avoid division by zero

NULLIF(a, b) returns NULL when a = b, otherwise a. Divide by NULLIF(x, 0) and a zero divisor gives NULL instead of a problem:

SELECT product_name, price, stock,       price / NULLIF(stock, 0) AS price_per_unit_in_stockFROM productsWHERE category = 'Electronics';

NULLIF also turns “empty” placeholder values into real NULLs, so they are skipped by aggregates:

CREATE TABLE survey (respondent INT, score INT);INSERT INTO survey VALUES (1, 8), (2, 0), (3, 6), (4, 0);SELECT AVG(score) AS avg_with_zeros, AVG(NULLIF(score, 0)) AS avg_answered_onlyFROM survey;

IF()

MySQL’s IF(condition, then, else) is handy for one quick test:

SELECT first_name, IF(commission IS NULL, 'Salary only', 'Salary + commission') AS pay_typeFROM employeesWHERE dept_id = 2 OR emp_id = 101;

For more than one condition use CASE.

SELECT first_name, COALESCE(dept_id, 0) AS deptFROM employeesORDER BY dept, first_name;

COALESCE and IFNULL FAQ