MySQL IFNULL function

The IFNULL function in MySQL is used to handle NULL values in a query by replacing them with a specified alternative value. This function is particularly useful when you want to display a default value or perform some other action if a column contains NULL.

Syntax

The basic syntax of the IFNULL function is as follows:

IFNULL(expr1, expr2)

expr1: This is the expression that you want to check for NULL. If expr1 is NULL, the function returns expr2.
expr2: This is the value that will be returned if expr1 is NULL.

Examples

Let’s consider a simple example where we have a table called employees with columns employee_id and employee_name. Some entries in the employee_name column may contain NULL values. We can use the IFNULL function to replace NULL values with a default name, such as “Unknown”.

SELECT 
employee_id, 
IFNULL(employee_name, 'Unknown') AS employee_name
FROM employees;

In this example, if the employee_name is NULL, it will be replaced with ‘Unknown’ in the result set. This ensures that you always get a non-NULL value for the employee_name column.

Return the product price if the product is in stock, or “Out of Stock” otherwise:

SELECT 
IFNULL(product_price, 'Out of Stock') 
FROM products 
WHERE in_stock = 1;

Update the customer address to the default address if the customer’s address is NULL:

UPDATE customers 
SET address = IFNULL(address, 'Default Address') 
WHERE address IS NULL;

Alternative: COALESCE Function

An alternative to IFNULL is the COALESCE function, which can handle multiple expressions and returns the first non-NULL expression in the list.

SELECT 
employee_id, 
COALESCE(employee_name, 'Unknown') AS employee_name
FROM employees;

In this case, if employee_name is NULL, it will be replaced with ‘Unknown’.

Both IFNULL and COALESCE are commonly used in MySQL to handle NULL values and provide default values or alternatives in query results. Choose the one that fits your needs and coding style.