MySQL ISNULL() function

Last update on May 23 2024 11:32:20 (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:


-- This query checks if the result of the expression (1 + 0) is NULL.
SELECT ISNULL(1 + 0);
-- The ISNULL() function returns 1 (true) if the expression is NULL, otherwise it returns 0 (false).

Explanation:

The purpose of this SQL query is to determine whether the result of the arithmetic expression 1 + 0 is NULL using the ISNULL function.

SELECT ISNULL(1 + 0): This part of the query evaluates the expression 1 + 0 and then applies the ISNULL function to check if the result is NULL.

The expression 1 + 0 evaluates to 1.

The ISNULL function checks if the result of the expression is NULL.

Since 1 is not NULL, the ISNULL function returns 0 (false).

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:


-- This query checks if the result of the expression (0 / 1) is NULL.
SELECT ISNULL(0 / 1);
-- The ISNULL() function returns 1 (true) if the expression is NULL, otherwise it returns 0 (false).
-- The expression 0 / 1 evaluates to 0, which is not NULL, so ISNULL(0 / 1) returns 0 (false).

Explanation:

The purpose of this SQL query is to determine whether the result of the arithmetic expression 0 / 1 is NULL using the ISNULL function.

SELECT ISNULL(0 / 1): This part of the query evaluates the expression 0 / 1 and then applies the ISNULL function to check if the result is NULL.

The expression 0 / 1 evaluates to 0.

The ISNULL function checks if the result of the expression is NULL.

Since 0 is not NULL, the ISNULL function returns 0 (false).

Output:

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

Slideshow of MySQL Comparison Function and Operators

Previous: IS
Next: LEAST()