MySQL IF & IFNULL

MySQL provides several compact functions for inline conditional logic and NULL handling. Unlike the CASE expression, these are single-purpose functions with a simpler syntax — ideal for quick decisions without writing a full CASE block.

IF()

IF(condition, value_if_true, value_if_false) — evaluates a condition and returns one of two values:

SELECT IF(500 > 100, 'yes', 'no');   -- 'yes'

-- Label orders as large or small based on amount
SELECT id, amount,
    IF(amount >= 3000, 'Large', 'Small') AS order_size
FROM orders;

IFNULL()

IFNULL(value, replacement) — if the first argument is NULL, return the replacement; otherwise return the original value:

SELECT IFNULL(NULL, 'default');   -- 'default'
SELECT IFNULL('hello', 'other');  -- 'hello'

-- Show 'No manager' instead of NULL in the manager column
SELECT name,
    IFNULL(manager_name, 'No manager') AS manager
FROM employees;

NULLIF()

NULLIF(value1, value2) — returns NULL if the two values are equal, otherwise returns the first value. It is useful for avoiding division-by-zero errors:

SELECT NULLIF(5, 5);    -- NULL  (values are equal)
SELECT NULLIF(5, 3);    -- 5     (values are different)

-- Safe division — returns NULL instead of an error when denominator is 0
SELECT total_revenue / NULLIF(total_orders, 0) AS avg_order_value
FROM sales_summary;

COALESCE()

COALESCE(val1, val2, val3, ...) — returns the first non-NULL value from its argument list. It is the most flexible NULL-replacement function because it accepts any number of arguments:

SELECT COALESCE(NULL, NULL, 'third', 'fourth');  -- 'third'
SELECT COALESCE(NULL, 42);                        -- 42

-- Use the first available contact method
SELECT name,
    COALESCE(mobile, phone, email, 'No contact') AS contact
FROM contacts;

COALESCE is the SQL standard way to handle NULL fallbacks. When you only have two values, IFNULL is a shorthand — IFNULL(a, b) is equivalent to COALESCE(a, b).

Quick Reference