MySQL ISNULL() Function

The MySQL ISNULL() function is used to test whether an expression is NULL. The function returns 1 if the expression is a NULL value. The function returns 0 if the expression is not a NULL value.

Syntax

ISNULL(expression)

Parameters

Return Value

Returns 1 if the expression is a NULL value. Returns 0 if the expression is not a NULL value.

Example 1:

The example below shows the usage of ISNULL() function.

mysql> SELECT ISNULL('Paris');
Result: 0

mysql> SELECT ISNULL(NULL);
Result: 1

mysql> SELECT ISNULL(5/0);
Result: 1

mysql> SELECT ISNULL(DATE('2018-10-15'));
Result: 0

mysql> SELECT ISNULL(DATE(NULL));
Result: 1

mysql> SELECT ISNULL('');
Result: 0

Example 2:

Consider a database table called Product with the following records:

If the OrderQuantity is optional and can contain NULL values. The statement mentioned below will give NULL value.

SELECT *, Price * (StockQuantity + OrderQuantity) AS Inventory  
FROM Product;

This will produce the result as shown below:

To avoid such situations, the ISNULL() function in conjunction with IF statement can be used to provide an alternative value to the column if it contains NULL value.

SELECT *, 
Price * (StockQuantity + IF(ISNULL(OrderQuantity), 0, OrderQuantity) AS Inventory
FROM Product;

This will produce the result as shown below: