MySQL IFNULL() and COALESCE()

MySQL IFNULL() and COALESCE(): Providing NULL Fallbacks

MySQL IFNULL() and COALESCE() replace NULL values with a safe fallback value so calculations and reports never break on missing data. While IFNULL() takes exactly two arguments, COALESCE() accepts two or more arguments and returns the first non-NULL value.

Syntax Comparison

-- MySQL-specific two-argument fallback
SELECT IFNULL(discount_amount, 0.00) FROM demo_orders;

-- ANSI SQL standard multi-argument fallback
SELECT COALESCE(shipping_phone, billing_phone, 'Not Provided')
FROM demo_customers;

Practical SQL Examples

The query below protects net total math on demo_orders from NULL discounts and picks the best available contact phone on demo_customers:

-- Protect arithmetic from NULL discounts
SELECT
    id,
    total_amount,
    IFNULL(discount_amount, 0.00) AS applied_discount,
    total_amount - COALESCE(discount_amount, 0.00) AS net_total
FROM demo_orders
ORDER BY id ASC;

Choosing Between IFNULL() and COALESCE()

• Prefer COALESCE() in Most Queries: COALESCE() is standard ANSI SQL (working in MySQL, PostgreSQL, and SQLite) and supports fallback chains like COALESCE(nick_name, first_name, 'Guest').
• Remember Empty Strings Are Not NULL: Neither IFNULL() nor COALESCE() replaces '' (empty string). To treat empty strings as missing too, write COALESCE(NULLIF(phone, ''), 'N/A').

Common Mistakes and Best Practices

• Watch Return Type Coercion: Mixing an integer column with a string fallback (COALESCE(age, 'Unknown')) converts numbers to strings across the entire output column; keep fallback types aligned with the column type.
• Avoid Wrapping Indexed Filter Columns: Writing WHERE COALESCE(status, 'pending') = 'pending' prevents MySQL from using an index on status. Filter explicitly with WHERE status = 'pending' OR status IS NULL.

Frequently Asked Questions

What is the difference between IFNULL() and ISNULL() in MySQL?

IFNULL(a, b) returns a if non-null or b otherwise, whereas ISNULL(a) returns 1 or 0 to indicate whether a is NULL.

Does COALESCE() evaluate all arguments or short-circuit?

COALESCE() evaluates arguments from left to right and stops as soon as it finds the first non-NULL value.

How do I fall back when a string column might be either NULL or ''?

Combine NULLIF(col, '') inside COALESCE: COALESCE(NULLIF(trim_col, ''), 'Default').