MySQL IFNULL and COALESCE

Both IFNULL() and COALESCE() are used in MySQL to handle NULL values by replacing them with a specified fallback value. They prevent NULL from appearing in query results and make output more readable and meaningful.

IFNULL()

IFNULL() takes two arguments. If the first argument is not NULL, it returns it as is. If it is NULL, it returns the second argument as the replacement.

Syntax

IFNULL(expression, replacement_value)

Example

SELECT IFNULL(NULL, 'Not Available');   -- Not Available
SELECT IFNULL('Delhi', 'Not Available'); -- Delhi

COALESCE()

COALESCE() accepts multiple arguments and returns the first non-NULL value from the list. If all arguments are NULL, it returns NULL.

Syntax

COALESCE(value1, value2, value3, ...)

Example

SELECT COALESCE(NULL, NULL, 'Third', 'Fourth'); -- Third
SELECT COALESCE(NULL, 'Second', 'Third');       -- Second
SELECT COALESCE('First', 'Second');             -- First

Sample Table

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    work_phone VARCHAR(15),
    personal_phone VARCHAR(15),
    city VARCHAR(50)
);

INSERT INTO employees VALUES (1, 'Ravi Kumar', '011-1234', NULL, 'Delhi');
INSERT INTO employees VALUES (2, 'Sneha Joshi', NULL, '9876543210', 'Mumbai');
INSERT INTO employees VALUES (3, 'Karan Das', NULL, NULL, NULL);
INSERT INTO employees VALUES (4, 'Pooja Nair', '080-5678', '9000011111', 'Bengaluru');

Example: IFNULL — Show City or Default

SELECT emp_name, IFNULL(city, 'City Not Provided') AS city
FROM employees;

Result:

emp_name    | city
------------+------------------
Ravi Kumar  | Delhi
Sneha Joshi | Mumbai
Karan Das   | City Not Provided
Pooja Nair  | Bengaluru

Example: COALESCE — First Available Phone Number

SELECT emp_name,
       COALESCE(work_phone, personal_phone, 'No Contact') AS contact
FROM employees;

Result:

emp_name    | contact
------------+------------
Ravi Kumar  | 011-1234
Sneha Joshi | 9876543210
Karan Das   | No Contact
Pooja Nair  | 080-5678

For each employee, COALESCE checks work_phone first. If it is NULL, it tries personal_phone. If both are NULL, it returns "No Contact".

IFNULL vs COALESCE

COALESCE in UPDATE

UPDATE employees
SET city = COALESCE(city, 'Unknown')
WHERE city IS NULL;

Key Points

• IFNULL(a, b) returns a if it is not NULL, otherwise returns b.
• COALESCE() returns the first non-NULL value from a list of arguments.
• COALESCE() is more flexible and standard; use it when checking multiple columns.
• Both functions are useful for providing fallback values in query output.
• Neither function modifies the data in the table — they only affect the query result.