Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Behavior of "NOT IN"


Behavior of "NOT IN"

Author
Message
S_Kumar_S
S_Kumar_S
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 1062
Hi
Here is sample script:
create table a(id int, nm varchar(200))

insert into a
select 1,'a'

insert into a
select 1,NULL

select * from a where nm not in('a')

I logically expect NULL records to be returned here, but they are not. What can be the alternative way to fetch the NULL records. Yes, I can add condition " OR a IS NULL' but why should I load my where caluse for a very logical requirement? Is their some setting which impacts this behavior?

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8921 Visits: 19005
S_Kumar_S (9/24/2012)
Hi
Here is sample script:
create table a(id int, nm varchar(200))

insert into a
select 1,'a'

insert into a
select 1,NULL

select * from a where nm not in('a')

I logically expect NULL records to be returned here, but they are not. What can be the alternative way to fetch the NULL records. Yes, I can add condition " OR a IS NULL' but why should I load my where caluse for a very logical requirement? Is their some setting which impacts this behavior?


It's all perfectly logical.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
S_Kumar_S
S_Kumar_S
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 1062
May be it's difference of perception but to me it looks illogical. I am asking just remove those records which are there in NOT IN, and i didn't mention NULL, so they should not be blocked.

ChrisM@Work (9/24/2012)
S_Kumar_S (9/24/2012)
Hi
Here is sample script:
create table a(id int, nm varchar(200))

insert into a
select 1,'a'

insert into a
select 1,NULL

select * from a where nm not in('a')

I logically expect NULL records to be returned here, but they are not. What can be the alternative way to fetch the NULL records. Yes, I can add condition " OR a IS NULL' but why should I load my where caluse for a very logical requirement? Is their some setting which impacts this behavior?


It's all perfectly logical.


-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47091 Visits: 44344
Add OR nm IS NULL

Null never matches any value, so you cannot have null returned from a in or not in.

Your IN is expanded to this:
NOT (nm='a')



Now, the logical expression NULL = 'literal value' returns UNKNOWN (not true, not false). NOT UNKNOWN is UNKNOWN. A where clause predicate will only return the row if the predicate returns TRUE. Hence, since both NULL = 'literal value' and NOT (NULL = 'literal value') both return UNKNOWN, not true, the row with null will never be returned from an IN or NOT IN.

The only logical expression that can return TRUE in the presence of NULL is the IS NULL/IS NOT NULL expression. Hence you will need to add that to your where clause if you want to see null values.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


S_Kumar_S
S_Kumar_S
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 1062
Thats a nice explanation. I got your point Gail. Thank you...


GilaMonster (9/24/2012)
Add OR nm IS NULL

Null never matches any value, so you cannot have null returned from a in or not in.

Your IN is expanded to this:
NOT (nm='a')



Now, the logical expression NULL = 'literal value' returns UNKNOWN (not true, not false). NOT UNKNOWN is UNKNOWN. A where clause predicate will only return the row if the predicate returns TRUE. Hence, since both NULL = 'literal value' and NOT (NULL = 'literal value') both return UNKNOWN, not true, the row with null will never be returned from an IN or NOT IN.

The only logical expression that can return TRUE in the presence of NULL is the IS NULL/IS NOT NULL expression. Hence you will need to add that to your where clause if you want to see null values.


-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Steven Willis
Steven Willis
SSC-Addicted
SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)

Group: General Forum Members
Points: 473 Visits: 1721
If your question was more than just theoretical, another way you could get the NOT 'a' values including NULLs would be to create a temporary column. I have no idea if this would perform better than just using 'OR IS NULL' in the WHERE clause based on this trivial example. I tried these two options against some real data and the query execution plans were the same except for a 'Compute Scalar' operation (due to the ISNULL operator) with an Estimated Operator Cost of 0%. It does seem in my experience though that using 'IS NULL' in a Where clause makes it difficult or impossible to get Index Seeks.



CREATE TABLE #a (id INT,nm VARCHAR(200))

INSERT INTO #a
SELECT
1
,'a'

INSERT INTO #a
SELECT
1
,NULL

SELECT
id
,nm
FROM
   (
   SELECT
    id
    ,nm
    ,ISNULL(nm,'xxx') AS nm2 --> 'xxx' = some always unique value or use NEWID()
   FROM
      #a
   Wink AS temp
WHERE
   nm2 NOT IN ('a')

DROP TABLE #a



GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47091 Visits: 44344
OR <column name> IS NULL does not prevent index seeks


(yes, I used an IN rather than a NOT IN, the NOT IN would have been a scan because of the size of the table and the distribution of data in it)

What can easily lead to scans is if you have something like WHERE Column1 = Something OR Column2 IS NULL
http://sqlinthewild.co.za/index.php/2011/05/03/indexing-for-ors/

Also, a NOT IN may well be evaluated as a scan whether or not the where clause is SARGable, because of the portion of the table that will be returned.

Be aware that the alternative with ISNULL is no longer SARGable and hence can't seek on an index even if the volume of data would allow it.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Attachments
Or with a seek.jpg (75 views, 124.00 KB)
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3934 Visits: 6660
S_Kumar_S (9/24/2012)
I logically expect NULL records to be returned here, but they are not. ... Is there some setting which impacts this behavior?



Yes there is a setting, but you should be extremely cautious about using it:

SET ANSI_NULLS OFF

create table a(id int, nm varchar(200))

insert into a
select 1,'a'

insert into a
select 1,NULL

select * from a where nm not in('a')

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47091 Visits: 44344
And if you do, note from Books Online:

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.



Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search