SQL Server : Challenges of Working with Nothing

If your data contains NULL values there will be deviations in result set, so the developer should be very careful while dealing with NULL values data.

Usually when comparing a column's data to a value or comparing column's data it results either in TRUE or FALSE, but comparision with NULL returns UNKNOWN which in somemeans same as FALSE.
We don't find the difference with '=' operator but with remaining operators like '<>' , the results will vary significantly as shown below.

USE AdventureWorks2008
GO
--Returns 504 rows
SELECT ProductID,Name
FROM Production.Product
GO
--Returns 248 rows
SELECT ProductID,Name
FROM Production.Product
WHERE Color IS NULL
GO
--Expected 478 rows but returns 230 rows
SELECT ProductID,Name,Style,Size,Color
FROM Production.Product
WHERE Color <> 'Blue'
GO
--Returns 478 rows
SELECT ProductID,Name,Style,Size,Color
FROM Production.Product
WHERE Color <> 'Blue' OR Color IS NULL
GO

We can observe the significance in result set due to NULL in last 2 query results.

Reference: DP (http://dptechnicalblog.blogspot.com/ )