SQL Tutorial SQL Advanced SQL Database SQL References

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

expression Required. Specify the value to test as NULL.

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:

ProductNamePriceStockQuantityOrderQuantity
Apple1.0010020
Banana1.2512030
Orange2.15105NULL
Watermelon3.507515

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

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

This will produce the result as shown below:

ProductNamePriceStockQuantityOrderQuantityInventory
Apple1.0010020120.0
Banana1.2512030187.5
Orange2.15105NULLNULL
Watermelon3.507515315.0

To avoid such situations, the ISNULL() function in conjunction with IF statement can be used to provides 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:

ProductNamePriceStockQuantityOrderQuantityInventory
Apple1.0010020120.0
Banana1.2512030187.5
Orange2.15105NULL225.75
Watermelon3.507515315.0

❮ MySQL Functions

5