Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Behavior of "NOT IN"
Add to briefcase

Behavior of "NOT IN" Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
S_Kumar_S
Posted Monday, September 24, 2012 4:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 322, Visits: 719
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
Post #1363408
ChrisM@Work
Posted Monday, September 24, 2012 4:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:11 AM
Points: 5,705, Visits: 11,132
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
Post #1363410
S_Kumar_S
Posted Monday, September 24, 2012 4:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 322, Visits: 719
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
Post #1363413
GilaMonster
Posted Monday, September 24, 2012 4:55 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 38,062, Visits: 30,359
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 2008, MVP
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
Post #1363414
S_Kumar_S
Posted Monday, September 24, 2012 5:10 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 322, Visits: 719
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
Post #1363419
Steven Willis
Posted Tuesday, September 25, 2012 9:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 5:43 PM
Points: 298, Visits: 1,320
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
	) AS temp
WHERE
	nm2 NOT IN ('a')
    
DROP TABLE #a
Post #1364132
GilaMonster
Posted Tuesday, September 25, 2012 10:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 38,062, Visits: 30,359
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 2008, MVP
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

  Post Attachments 
Or with a seek.jpg (74 views, 124.75 KB)
Post #1364176
ScottPletcher
Posted Tuesday, September 25, 2012 10:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:11 AM
Points: 1,333, Visits: 1,803
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)
One man with courage makes a majority. Andrew Jackson
Post #1364180
GilaMonster
Posted Tuesday, September 25, 2012 10:49 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 38,062, Visits: 30,359
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 2008, MVP
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
Post #1364182
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.