Select statement and NULL values

When you are writing a Select statement (in SQL Server) and you want to equate a value to NULL you have to use the IS (IS NOT) NULL clause.

SELECT *
FROM Production.Product
WHERE Color IS NOT NULL

If you replace “IS NOT” with “<>” your query will not return any rows. This happens when the ANSI_NULLS setting is set to ON (The default setting is ON).

But if you need to replace “IS NOT” then you have to set ANSI_NULLS to OFF. In this way your Select will work fine.

SET ANSI_NULLS OFF

SELECT *
FROM Production.Product
WHERE Color <> NULL

Advertisements