Re: Selecting a record froma table where a column might be null

From: Chuck Fox (chuckfox2_at_aol.com)
Date: 04/30/04 

Date: Fri, 30 Apr 2004 09:01:06 -0400
To: mpeppler@peppler.org

mpeppler@peppler.org wrote:

>On Thu, 2004-04-29 at 23:20, Chuck Fox wrote:
>
>
>>Hmmm,
>>
>>So column != column when the column contains a null. How very unusual.
>>I would have assumed that null = null, but there I go assuming things
>>again.
>>
>>So why does this work ?
>>
>>create table foo( i int, c char(10) null )
>>go
>>insert foo values( 1, null )
>>insert foo values( 2, "2" )
>>go
>>
>>select * from foo where c = null
>>go
>>declare @c char(10)
>>select @c = null
>>select * from foo where c = isnull( @c, c )
>>go
>>
>>
>
>If you are using Sybase then the default behavior is to allow comparison
>to NULL. This is NOT ANSI SQL compliant, and one should use "where foo
>is NULL" instead of "where foo = NULL" to be on the safe side.
>
>This behavior is controlled by the ANSINULL option - see the SET T-SQL
>command.
>
>Michael
>
>
Michael and others

Thanks for the lesson in non-compliant behavior in one's favorite
product. I commonly use this trick to solve the exact problem the
original questioner posted and assumed the technique would be as
universal as duct tape. Sadly, this is not the case. However, another
poster, Alan, did resolve the issue in the Oracle fashion with a
technique that was quite similar in nature.

Try to add nvl function to both side of equal in the where clause.

eg. where nvl(column, '~') = nvl(?, '~')

Bad case of the heart was in the right place, but the head was in the
wrong db.

Your Friendly Neighborhood DBA,

Chuck