Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

NOT IN not equal to <>

Hi!

I am between session of 24 hour of PASS right now, so I just post this piece of code I helped out with on a forum.
The original poster was amazed that

Col1 <> 'Some value'

didn't return same records as

Col1 NOT IN ('Some value')

See this example code to understand the implications of fiddling with SET ANSI_NULLS option setting.


declare
@sample table
        (
            d varchar(200)
        )

Insert  @sample
select  'cancelled' union all
select  null

-- Try 1
set ansi_nulls off

select  d as [Try 1, <>, ANSI_NULLS off]
from    @sample
where   d <> 'cancelled'

select  d as [Try 1, NOT IN, ANSI_NULLS off]
from    @sample
where   d not in ('cancelled')

-- Try 2
set ansi_nulls on

select  d as [Try 2, <>, ANSI_NULLS on]
from    @sample
where   d <> 'cancelled'

select  d as [Try 2, NOT IN, ANSI_NULLS on]
from    @sample
where   d not in ('cancelled')

 

Print | posted on Wednesday, September 02, 2009 4:07 PM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Feedback

Gravatar

# re: NOT IN not equal to <>

According to BOL

For a script to work as intended, regardless of the ANSI_NULLS database option or the setting of SET ANSI_NULLS, use IS NULL and IS NOT NULL in comparisons that might contain null values.

9/8/2009 5:33 PM | Peter Cwik
Gravatar

# re: NOT IN not equal to <>

The issue is not to get the NULL values. The issue OP had was why NULL values were returned at all.
9/8/2009 8:01 PM | Peso
Gravatar

# re: NOT IN not equal to <>

Phenomenal bkredaown of the topic, you should write for me too!
8/23/2011 10:24 AM | Dayana
Gravatar

# re: NOT IN not equal to <>

bLiVei huqdjakfufmc
8/27/2011 1:12 PM | mhstyhcfl
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET