MySQL: IFNULL Function

This MySQL tutorial explains how to use the MySQL IFNULL function with syntax and examples.

Description

The MySQL IFNULL function allows you to return an alternate value if an expression is NULL.

Syntax

The syntax for the IFNULL function in MySQL is:

IFNULL( expression, value_if_null )

Parameters or Arguments

expression

The value to test as NULL.

value_if_null

The value to return if expression is NULL.

Note

• The IFNULL function will return expression, if expression is NOT NULL.
• The IFNULL function will return value_if_null, if expression is NULL.
• The IFNULL function is similar to the Nz function in MSAccess.

Applies To

The IFNULL function can be used in the following versions of MySQL:

• MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23

Example

Let's look at some MySQL IFNULL function examples and explore how to use the IFNULL function in MySQL.

For example:

mysql> SELECT IFNULL('techonthenet.com', 'checkyourmath.com');
Result: 'techonthenet.com'

mysql> SELECT IFNULL(NULL, 'checkyourmath.com');
Result: 'checkyourmath.com'

mysql> SELECT IFNULL(DATE('2014-02-01'), '2014-02-18');
Result: '2014-02-01'

mysql> SELECT IFNULL(DATE(NULL), '2014-02-18');
Result: '2014-02-18'

mysql> SELECT IFNULL(5, 6);
Result: 5

mysql> SELECT IFNULL(5/0, 'Dividing by 0 returns NULL');
Result: 'Dividing by 0 returns NULL'