MySQL ISNULL() function

Last update on September 16 2023 07:52:59 (UTC/GMT +8 hours)

ISNULL() function

MySQL ISNULL() function returns 1 when the expression is NULL otherwise it returns 0.

This function is useful in -

Using ISNULL() makes queries more readable and understandable, as it explicitly states the condition being checked.
In databases, it's common for certain fields to have NULL values, especially when the data is optional.
It can be used to substitute NULL values with a default value, ensuring that a specific value is always present in the result set.
When used in conjunction with aggregate functions like COUNT, SUM, AVG, etc., ISNULL() allows for precise calculations that exclude or include NULL values as needed.
In join operations, especially left joins, ISNULL() is often used to identify rows that do not have corresponding entries in the joined table.
It allows for conditional logic based on whether a value is NULL or not.
It helps in avoiding errors that can occur when attempting operations (like arithmetic) on NULL values.

Syntax:

ISNULL(expr)

MySQL Version: 8.0

Example: ISNULL() function with non-null value

In the following MySQL statement, given argument is a non-NULL value. So , ISNULL function returns 0.

Code:

SELECT ISNULL(1+0);

Output:

mysql> SELECT ISNULL(1+0);
+-------------+
| ISNULL(1+0) |
+-------------+
|           0 | 
+-------------+
1 row in set (0.03 sec)

Example : ISNULL() function with NULL value

In the following MySQL statement, given argument is a non-NULL value. So , ISNULL function returns 0.

Code:

SELECT ISNULL(0/1);

Output:

mysql> SELECT ISNULL(0/1);
+-------------+
| ISNULL(0/1) |
+-------------+
|           0 | 
+-------------+
1 row in set (0.00 sec)

Previous: IS
Next: LEAST()