MySQL IFNULL() Function

The IFNULL() function in MySQL is used to test whether an expression is NULL. If it is NULL, it returns a specified alternative value. Otherwise, it returns the expression itself.

Syntax

IFNULL(expression, alt_value)

Parameter Values

• expression: The expression to test whether it is NULL.
• alt_value: The value to return if the expression is NULL.

Definition and Usage

• The IFNULL() function is used to handle NULL values in SQL queries by providing a fallback or default value if the tested expression is NULL.
• It is useful for avoiding NULL results in queries and ensuring that default or meaningful values are returned instead.

Return Value

• If the expression is NULL, it returns the alt_value.
• If the expression is NOT NULL, it returns the expression itself.

Technical Details

• Works in: From MySQL 4.0

Examples

Example 1: Expression is NULL

Return the specified value "W3Schools.com" if the expression is NULL, otherwise return the expression:

SELECT IFNULL(NULL, "W3Schools.com");

Example Output:

'W3Schools.com'

Example 2: Expression is NOT NULL

Return the specified value "W3Schools.com" if the expression is NULL, otherwise return the expression:

SELECT IFNULL("Hello", "W3Schools.com");

Example Output:

'Hello'

Example 3: Numeric Value

Return 500 if the expression is NULL, otherwise return the expression:

SELECT IFNULL(NULL, 500);

Example Output:

500

Usage Notes

• The IFNULL() function is particularly useful when working with data that may contain NULL values, allowing you to ensure that your queries always return meaningful data instead of NULL. It is frequently used in combination with SELECT statements to clean or transform data.