NOT IN excludes NULL values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

NOT IN excludes NULL values

Author

Topic

sweta_singh
Starting Member

10 Posts

Posted - 2009-09-01 : 06:07:59

Hi,
I have used a simple NOT IN statement in my query inside a function, but somehow this statement excludes NULL values too in the result set.I then add an or condition to include NULL values too but then the processing time is 12-13 seconds as opposed to 1 sec without the OR condition. Can anyone please tell me why the NOT IN statement excludes NULL values too from the result set?

My query's WHERE clause goes something like this

WHERE q.QuoteID = @QuoteID
and s.FunctionCode Not IN(13,19) or s.FunctionCode IS NULL

FunctionCode is of type INT

Thanks in advance.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-01 : 06:15:40

try
and isnull(s.FunctionCode,-1) Not IN(13,19)

No, you're never too old to Yak'n'Roll if you're too young to die.

sweta_singh
Starting Member

10 Posts

Posted - 2009-09-01 : 06:24:12

Thanks. This works just fine with the processing time as 1 sec.I shall use this. Just curious to know why NOT IN excludes NULL values too.

Thanks and regards,
Sweta

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-01 : 06:26:45

to be on safer side

and isnull(s.FunctionCode,-999999) Not IN(13,19)

Madhivanan

Failing to plan is Planning to fail

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-09-01 : 06:46:03

http://www.sqlmag.com/Articles/ArticleID/96563/96563.html?Ad=1

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2009-09-01 : 06:54:39

Please be aware that if you wrap the column around in function as in this case, ISNULL(), there is a chance that if the index exist on that column, it will not be used forcing a table scan.

In which case, you have to resort to earlier IS NULL alternative. Also, whenever using OR make sure you wrap it in parenthesis.

WHERE q.QuoteID = @QuoteID 
and (s.FunctionCode Not IN(13,19) or s.FunctionCode IS NULL)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-01 : 07:01:16

quote:
Originally posted by harsh_athalye

Please be aware that if you wrap the column around in function as in this case, ISNULL(), there is a chance that if the index exist on that column, it will not be used forcing a table scan.

In which case, you have to resort to earlier IS NULL alternative. Also, whenever using OR make sure you wrap it in parenthesis.

WHERE q.QuoteID = @QuoteID
and (s.FunctionCode Not IN(13,19) or s.FunctionCode IS NULL)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Where were you for long time?

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2009-09-01 : 07:07:22

Hi Madhi,

Good to see you still in action :) It was hell busy here, went through merger and still struggling with the chaos resulted ;)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sweta_singh
Starting Member

10 Posts

Posted - 2009-09-01 : 07:19:18

Ok, thanks a lot everybody!:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-01 : 08:59:44

quote:
Originally posted by harsh_athalye

Hi Madhi,

Good to see you still in action :) It was hell busy here, went through merger and still struggling with the chaos resulted ;)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Ok. Fine

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources