MySQL ISNULL() function

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()