SQL Server Tutorial SQL Server Advanced SQL Server Database SQL Server References

SQL Server ISNULL() Function



The SQL Server (Transact-SQL) ISNULL() function is used to test whether an expression is NULL. The function replaces NULL with the specified replacement value. The function returns the expression if it is not a NULL value.

Syntax

ISNULL(expression, replacement_value)

Parameters

expression Required. Specify the value to test as NULL.
replacement_value Required. Specify the replacement value for NULL.

Return Value

Returns replacement_value if the expression is a NULL value. Returns expression if it is not a NULL value.

Example 1:

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

SELECT ISNULL('Paris', 'London');
Result: 'Paris'

SELECT ISNULL(NULL, 'London');
Result: 'London'

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 statement 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 can be used to provide an alternative value to the column if it contains NULL value.

SELECT *, 
Price * (StockQuantity + ISNULL(OrderQuantity, 0)) 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

❮ SQL Server Functions