IFNULL and COALESCE in MySQL

Introduction

These functions help you replace missing or empty values with something useful, so your data looks complete and clear.

When you want to show a default value instead of NULL in reports.

When adding numbers that might have missing values and you want to avoid errors.

When combining multiple columns and you want the first non-empty value.

When cleaning up data for display on a website or app.

When you want to avoid NULL results in calculations or text.

Syntax

MySQL

IFNULL(expression, replacement_value)
COALESCE(expression1, expression2, ..., expressionN)

IFNULL checks one expression and returns the replacement if it is NULL.

COALESCE checks multiple expressions and returns the first one that is not NULL.

Examples

Returns 'No data' because the first value is NULL.

MySQL

SELECT IFNULL(NULL, 'No data');

Returns 'First non-null' because it is the first non-NULL value.

MySQL

SELECT COALESCE(NULL, NULL, 'First non-null', 'Second');

Returns 5 because it is not NULL.

MySQL

SELECT IFNULL(5, 10);

Returns 0 because it is the first non-NULL value.

MySQL

SELECT COALESCE(NULL, 0, 1);

Sample Program

This example shows how IFNULL replaces NULL prices or discounts with 0, so calculations work without errors. COALESCE finds the first available price or discount.

MySQL

CREATE TABLE products (
  id INT,
  name VARCHAR(20),
  price DECIMAL(5,2),
  discount DECIMAL(5,2)
);

INSERT INTO products VALUES
(1, 'Pen', 1.50, NULL),
(2, 'Notebook', 3.00, 0.50),
(3, 'Eraser', NULL, NULL);

SELECT
  name,
  IFNULL(price, 0) AS price,
  IFNULL(discount, 0) AS discount,
  IFNULL(price, 0) - IFNULL(discount, 0) AS final_price,
  COALESCE(price, discount, 0) AS first_available_price
FROM products;

Important Notes

IFNULL is simpler but only works with two values.

COALESCE is more flexible and can check many values.

Both help avoid NULL showing up in your results, which can confuse users.

Summary

Use IFNULL to replace NULL with one default value.

Use COALESCE to find the first non-NULL value among many.

They make your data easier to read and safer to calculate with.