RSS

MS SQL Server WHERE column = NULL

25 Mar

In order to check, in Microsoft SQL Server, whether a condition is NULL you cannot use:

SELECT something FROM somewhere WHERE column = NULL
SELECT something FROM somewhere WHERE column  NULL

Instead you have to use following pattern:

SELECT something FROM somewhere WHERE column IS NULL
--or if desired
SELECT something FROM somewhere WHERE column IS NOT NULL
Advertisements
 
4 Comments

Posted by on March 25, 2010 in SQL

 

Tags:

4 responses to “MS SQL Server WHERE column = NULL

  1. swe

    January 5, 2011 at 11:40

    Hi,

    you should not use the code posted above. Instead you should use:

    SELECT something FROM somewhere WHERE column IS NULL

    Otherwise you get Rows, where column is null AND rows where column is empty string!

     
  2. Florian Reischl (@Florian_Reischl)

    August 30, 2011 at 21:31

    If ever needed (no suggestion though), you can use turn ansi nulls of:

    SET ANSI_NULLS OFF;

    After this you can do “WHERE MyColumn = NULL” for the lifetime of the current connections session. In .NET until the connection becomes sent back to connection pool in .NET.

    Greets
    Flo

     
    • michaelmairegger

      August 31, 2011 at 08:33

      Thanks for the comment.
      Yes that would be a option. But reading SQL-Documentation it would be a bad idea:

      In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

      So I suggest using IS NULL or IS NOT NULL to avoid conflicts in future SQL-Servers.

       

Leave a Reply

Please log in using one of these methods to post your comment:

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: