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

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:

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 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: