SQL 'Is Null' Statement Explained

This blog explains the use of the SQL 'Is Null' statement.

Introduction 

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

Example

 

 

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;

Summary

In this blog, you learned how to use a SQL IsNull statement with various options.