NULL Handling Functions in SQL: IFNULL, NULLIF, and COALESCE

The IS NULL and IS NOT NULL operators, covered back in Module 3, let you test for NULL in a WHERE clause — but what about gracefully substituting a friendly default value, or detecting when two values happen to be equal? IFNULL, NULLIF, and COALESCE are MySQL's dedicated functions for exactly these everyday NULL-handling needs.

What Do These NULL-Handling Functions Do?

IFNULL(expr, default_value) returns default_value if expr is NULL, otherwise returns expr unchanged. NULLIF(expr1, expr2) returns NULL if the two expressions are equal, otherwise returns expr1. COALESCE(val1, val2, ..., valN) returns the first non-NULL value among any number of arguments, checked left to right.

What You'll Learn

• Substitute a default display value for NULL using IFNULL.
• Detect equal values and convert them to NULL using NULLIF.
• Use COALESCE to pick the first available value from several columns.
• Choose the right function among IFNULL, NULLIF, and COALESCE for a given scenario.

Key Terms to Know

• IFNULL: Returns a default value if an expression is NULL.
• NULLIF: Returns NULL if two expressions are equal, otherwise returns the first expression.
• COALESCE: Returns the first non-NULL value among a list of expressions.

IFNULL: Substituting a Default Value for NULL

IFNULL(phone_number, 'Not provided') returns the actual phone_number value if it isn't NULL, or the string 'Not provided' if it is — a clean way to avoid displaying blank or NULL values directly to end users. This is one of the most frequently used NULL-handling functions in everyday reporting and display queries.

IFNULL is MySQL-specific; the ANSI SQL standard equivalent is COALESCE(expr, default_value) with exactly two arguments, which behaves identically for this simple two-argument case.

NULLIF: Converting Equal Values into NULL

NULLIF(stock_qty, 0) returns NULL if stock_qty equals 0, otherwise returns stock_qty unchanged. This is useful for deliberately treating a specific 'sentinel' value (like 0 representing 'not applicable' rather than a real zero) as NULL for calculation purposes — for instance, NULLIF can prevent a division by zero error by converting a zero denominator into NULL first, since dividing by NULL safely returns NULL rather than raising an error.

COALESCE: Picking the First Available Value

COALESCE(mobile_phone, work_phone, home_phone, 'No phone on file') checks each argument left to right and returns the first one that isn't NULL, falling through to the final fallback string only if every preceding value is NULL. This is especially useful when a result could come from any of several optional columns, each representing a fallback option in priority order.

Unlike IFNULL, which only accepts exactly two arguments, COALESCE accepts any number of arguments, making it the more flexible and broadly portable choice (it's also part of the ANSI SQL standard, supported across virtually all major database systems, unlike IFNULL which is MySQL/MariaDB-specific).

Visual Summary

Picture a customer record with several possible phone number fields, most of which are usually blank. IFNULL is like checking one specific field and using a generic placeholder note if it's blank. NULLIF is like deliberately blanking out a field if it happens to contain a specific 'meaningless' placeholder value, like a phone number of all zeros. COALESCE is like checking a whole prioritized list of fields one at a time — mobile first, then work, then home — and using whichever one is actually filled in first.

IFNULL vs NULLIF vs COALESCE

Function	Arguments	Behavior
IFNULL(a, b)	Exactly 2	Returns b only if a is NULL, otherwise returns a
NULLIF(a, b)	Exactly 2	Returns NULL if a equals b, otherwise returns a
COALESCE(a, b, ..., n)	2 or more	Returns the first non-NULL value in the list

SQL Example

-- IFNULL: friendly default for a missing phone number
SELECT customer_id, IFNULL(phone_number, 'Not provided') AS phone_display
FROM customers;

-- NULLIF: avoid a division-by-zero error when calculating a per-unit rate
SELECT order_id, total_amount, quantity,
  total_amount / NULLIF(quantity, 0) AS price_per_unit
FROM order_items;

-- COALESCE: pick the first available contact number from several options
SELECT customer_id,
  COALESCE(mobile_phone, work_phone, home_phone, 'No phone on file') AS best_contact
FROM customers;

The first query displays a friendly fallback whenever phone_number is NULL. The second uses NULLIF to convert a zero quantity into NULL before dividing, so the division safely returns NULL instead of raising a division-by-zero error. The third checks three possible phone columns in priority order, falling back to a clear message only if all three are NULL.

Real-World Examples

• Customer support dashboards use IFNULL to display 'Not provided' or similar friendly placeholders instead of blank or NULL fields.
• Financial reporting queries use NULLIF to safely guard against division-by-zero errors when calculating ratios or per-unit rates.
• CRM systems use COALESCE across multiple contact fields (mobile, work, home) to determine the single best available contact method per customer.
• Data migration scripts use COALESCE to merge values from several legacy source columns into one clean, unified column during a schema consolidation.

Best Practices and Pro Tips

• Reach for COALESCE over IFNULL whenever more than two fallback options are needed, or when writing SQL intended to be portable across different database systems beyond just MySQL.
• Use NULLIF specifically as a guard against division-by-zero errors in calculated columns — it's a small, clean pattern worth memorizing.
• Be mindful that IFNULL and COALESCE return a value with a data type MySQL infers from the arguments, which can occasionally cause unexpected type coercion if the arguments have different types — test edge cases when mixing numeric and text fallbacks.

Common Mistakes to Avoid

• Using IFNULL when more than two fallback options are actually needed, requiring an awkward nested IFNULL chain instead of a single, cleaner COALESCE call.
• Forgetting that NULLIF(a, b) only returns NULL when a equals b — it does not check whether a or b themselves are NULL in any special way beyond that equality check.
• Assuming IFNULL is available in every SQL database — it's MySQL/MariaDB-specific, and COALESCE should be used instead for portable SQL.
• Using division without NULLIF protection on a column that could legitimately be zero, resulting in a runtime division-by-zero error instead of a clean NULL result.

Interview Questions

Q1. What is the difference between IFNULL and COALESCE?

IFNULL accepts exactly two arguments and returns the second if the first is NULL. COALESCE accepts any number of arguments and returns the first non-NULL value among them, making it more flexible and also part of the ANSI SQL standard, unlike the MySQL-specific IFNULL.

Q2. How does NULLIF help prevent a division-by-zero error?

NULLIF(denominator, 0) converts a zero denominator into NULL before the division happens. Since dividing by NULL in SQL returns NULL rather than raising an error, this safely avoids the division-by-zero error entirely.

Q3. If you needed to check four different columns in priority order for the first available value, which function would you use?

COALESCE, since it accepts any number of arguments and returns the first non-NULL value checked from left to right, unlike IFNULL which only supports exactly two arguments.

Q4. What does NULLIF(stock_qty, 0) return if stock_qty is 5?

It returns 5 unchanged, since NULLIF only returns NULL when its two arguments are equal; since 5 does not equal 0, the original value passes through untouched.

Practice MCQs

1. Which function is MySQL-specific and accepts exactly two arguments?

1. COALESCE
2. NULLIF
3. IFNULL
4. All three are MySQL-specific

Answer: C. IFNULL

Explanation: IFNULL is a MySQL/MariaDB-specific function accepting exactly two arguments, while COALESCE and NULLIF are part of the ANSI SQL standard.

2. What does COALESCE(NULL, NULL, 'fallback') return?

1. NULL
2. An error
3. 'fallback'
4. Empty string

Answer: C. 'fallback'

Explanation: COALESCE returns the first non-NULL value among its arguments, so it skips both NULL values and returns 'fallback'.

3. Which function would you use to safely avoid a division-by-zero error?

1. IFNULL
2. NULLIF
3. COALESCE
4. None of these prevent it

Answer: B. NULLIF

Explanation: NULLIF(denominator, 0) converts a zero denominator into NULL, and dividing by NULL returns NULL instead of raising a division-by-zero error.

Quick Revision Points

• IFNULL(a, b): returns b only if a is NULL; MySQL-specific, exactly 2 arguments.
• NULLIF(a, b): returns NULL if a equals b, otherwise returns a.
• COALESCE(a, b, ..., n): returns the first non-NULL value; ANSI standard, any number of arguments.
• NULLIF is a common guard pattern against division-by-zero errors.

Conclusion

• IFNULL, NULLIF, and COALESCE each solve a distinct, common NULL-handling need.
• COALESCE is the more flexible and portable choice whenever more than two fallback values are involved.
• NULLIF's division-by-zero guard pattern is a small but genuinely valuable technique to remember.

IFNULL, NULLIF, and COALESCE round out SQL's NULL-handling toolkit beyond simple IS NULL checks: IFNULL substitutes a default for a single NULL value, NULLIF converts matching values into NULL (commonly to guard against division by zero), and COALESCE picks the first available value from any number of fallback options. Together they let queries gracefully handle incomplete data without extra application-side logic. The next lesson covers conditional functions — IF() and CASE WHEN — for branching logic directly inside SQL expressions.