The 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;