T-SQL ISNULL() Function
The T-SQL (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:
ProductName | Price | StockQuantity | OrderQuantity |
---|---|---|---|
Apple | 1.00 | 100 | 20 |
Banana | 1.25 | 120 | 30 |
Orange | 2.15 | 105 | NULL |
Watermelon | 3.50 | 75 | 15 |
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:
ProductName | Price | StockQuantity | OrderQuantity | Inventory |
---|---|---|---|---|
Apple | 1.00 | 100 | 20 | 120.0 |
Banana | 1.25 | 120 | 30 | 187.5 |
Orange | 2.15 | 105 | NULL | NULL |
Watermelon | 3.50 | 75 | 15 | 315.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:
ProductName | Price | StockQuantity | OrderQuantity | Inventory |
---|---|---|---|---|
Apple | 1.00 | 100 | 20 | 120.0 |
Banana | 1.25 | 120 | 30 | 187.5 |
Orange | 2.15 | 105 | NULL | 225.75 |
Watermelon | 3.50 | 75 | 15 | 315.0 |
❮ T-SQL Functions