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)