April 8, 2020 07:41 by
PeterThe SQL 'Is Null' statement returns the specified value if the expression is NULL, otherwise, it returns the expression. If the expression is NOT NULL, then this function returns the expression.
Syntax
SELECT ISNULL(NULL, 'Csharpcorner');
This replaces Null with the specified replacement value.
A null statement doesn't specify that the Boolean result is negated. The predicate reverses its return values, returning True if the value is not Null, and False if the value is a Null statement.
Syntax
ISNULL ( check_expression , replacement_value )
This is the expression returned if the check_expression is NULL. The replacement_value must be of a type that is implicitly convertible to the type of check_expression.
Using IsNull with Avg in SQL statement
The following example finds the average of the OrderDetails. It substitutes the value 8 for all NULL entries in the orderId column of theOrderDetails table.
Syntax
USE sample ;
GO
SELECT AVG(ISNULL(OrderId, 8))
FROM OrderDetails ;
GO
Example
Using ISNULL in SQL
The following example selects the description, discount percentage, minimum quantity, and maximum quantity for all special offers in the sample. If the maximum quantity for a particular special offer is NULL, the MaxQty shown in the result set is 0.00.
Syntax
USE sample ;
GO
SELECT OrderId , OrderName, Orderaddress , ISNULL(OrderId, 0.00) AS 'Max Quantity'
FROM OrderDetails;
GO
Testing for NULL in a Where clause
Do not use ISNULL to find NULL values; use IS NULL instead. The following example finds all OrderDetails that have NULL in the ordername, orderAddress, OrderDate column. Note the space between IS and NULL.
Syntax
-- Uses sample
SELECT OrderName, orderAddress, OrderDate
FROM OrderDetails
WHERE OrderId IS NULL;