How to...search for NULL values(Kristofer Gafvert, October 13, 2009)
A null value means that the data is unknown. It does not mean that the value is blank or zero and two null values are not equal. Because of this, we need to use a special operator to work with NULL values, called the "IS" operator.
WHERE AddressLine2 IS NOT NULL
The above example selects all rows where AddressLine2 is not null, from the Person.Address table in the sample database AdventureWorks. If you want to search for all rows where AddressLine2 is null, you would write:
WHERE AddressLine2 IS NULL
Do note that although SQL Server Management Studio shows null values with the text "Null", it is not a text value, and you should not enclose Null with apostrophes.
Applies to [?]
SQL Server 2008