SQL Server Working with NULLs

A NULL value can be tricky to code around because its value is unknown. SQL Server 2005 provides
functions used to handle NULLs in your code

Table  NULL Functions

Function	Description
ISNULL	ISNULL validates if an expression is NULL, and if so, replaces the NULL value with an alternate value.
COALESCE	The COALESCE function returns the first non-NULL value from a provided list of expressions.
NULLIF	NULLIF returns a NULL value when the two provided expressions have the same value. Otherwise the first expression is returned.

These next few recipes will demonstrate these functions in action.

Replacing a NULL Value with an Alternative Value
ISNULL validates if an expression is NULL, and if so, replaces the NULL value with an alternate value.
In this example, any NULL value will be replaced with a different value:

SELECT LastName,
ISNULL(Title, 'UNKNOWN') Title
FROM Person.Contact
WHERE LastName LIKE 'Sa%'

This returns the following (abridged) results:

How It Works ?
In this example, the LastName column and Samant had a NULL value for the Title column. The ISNULL function replaced that NULL value with the UNKNOWN string expression in the query results.


Performing Flexible Searches Using ISNULL
In this recipe, I’ll demonstrate how to perform flexible, dynamic searches in a query when the variables
may or may not be populated. This recipe declares three local search variables for ProductID,
StartDate, and StandardCost. By using this technique, your query can return results based on all,
, only a ProductID is supplied:


-- Local variables used for searches
DECLARE @ProductID int
DECLARE @StartDate datetime
DECLARE @StandardCost money
-- Only @ProductID is used
SET @ProductID = 711
SELECT ProductID, StartDate, StandardCost
FROM Production.ProductCostHistory
WHERE ProductID = ISNULL(@ProductID, ProductID) AND
StartDate = ISNULL(@StartDate, StartDate) AND
StandardCost = ISNULL(@StandardCost, StandardCost)

This returns:

In this second example, a search is performed by aminimum and maximum StandardCost range:
-- Local variables used for searches
DECLARE @ProductID int
DECLARE @MinStandardCost money
DECLARE @MaxStandardCost money
SET @MinStandardCost = 3.3963
SET @MaxStandardCost = 10.0000
SELECT ProductID, StartDate, StandardCost
FROM Production.ProductCostHistory
WHERE ProductID = ISNULL(@ProductID, ProductID) AND
StandardCost BETWEEN ISNULL(@MinStandardCost, StandardCost) AND
ISNULL(@MaxStandardCost, StandardCost)
ORDER BY StandardCost

This returns the following (abridged) results:

ProductID StartDate StandardCost
709 2001-07-01 00:00:00.000 3.3963
710 2001-07-01 00:00:00.000 3.3963
712 2001-07-01 00:00:00.000 5.7052
846 2002-07-01 00:00:00.000 5.7709
...
932 2003-07-01 00:00:00.000 9.3463
860 2002-07-01 00:00:00.000 9.7136
859 2002-07-01 00:00:00.000 9.7136
858 2002-07-01 00:00:00.000 9.7136
How It Works
The benefit of the method demonstrated in this recipe is that your code will be more flexible, allowing
for data to be searched in myriad ways, and keeping each search condition optional. The key to

this recipe is in the WHERE clause. Each search condition uses ISNULL and the local variable name, followed
by the column name itself:
WHERE ProductID = ISNULL(@ProductID, ProductID) AND
StartDate = ISNULL(@StartDate, StartDate) AND
StandardCost = ISNULL(@StandardCost, StandardCost)
If a parameter is not SET, it will remain NULL, and thus the search condition for each column
will evaluate the column value against itself—always returning TRUE. Only the parameters that have
been specified will be used to filter the results.

Returning the First Non NULL Value in a List of Expressions
The COALESCE function returns the first non-NULL value from a provided list of expressions. The
syntax is:
COALESCE ( expression [ ,...n ] )
This recipe demonstrates how to use COALESCE to return the first occurrence of a non-NULL value:
DECLARE @Value1 int
DECLARE @Value2 int
DECLARE @Value3 int
SET @Value2 = 22
SET @Value3 = 955
SELECT COALESCE(@Value1, @Value2, @Value3)
This returns:
22
How It Works
In this recipe, three local variables were created: @Value1, @Value2, and @Value3. Only @Value2 and
@Value3 were SET to actual integer values. The variable not SET to a value, @Value2, is NULL. In COALESCE,
the three values were checked, from @Value1 to @Value3. Since the @Value2 variable was the first variable
with a non-NULL value, “22” was returned.

Returning a NULL Value When Two Expressions Are Equal:
Otherwise Return the First Expression
NULLIF returns a NULL value when the two provided expressions have the same value; otherwise the
first expression is returned.
This example demonstrates how to use NULLIF to evaluate two expressions. If the two expressions
are equal, a NULL value will be returned, otherwise the first evaluated expression is returned:
DECLARE @Value1 int
DECLARE @Value2 int
SET @Value1 = 55
SET @Value2 = 955
SELECT NULLIF(@Value1, @Value2)

55
The next example tests the values when both are equal:
DECLARE @Value1 int
DECLARE @Value2 int
SET @Value1 = 55
SET @Value2 = 55
SELECT NULLIF(@Value1, @Value2)
This returns:
NULL
How It Works
In this recipe, the first batch had two differing values: 55 and 955. Since @Value1 was evaluated first
and the values were different, the NULLIF condition is FALSE, and the first evaluated value is returned.
In the second batch, both @Value1 and @Value2 were equal, so NULLIF returned a NULL value instead.