Select statement and NULL values

13 03 2011

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

Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




%d bloggers like this: