Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Behavior of "NOT IN" Expand / Collapse
Author
Message
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: 2 days ago @ 11:18 AM
Points: 380, Visits: 903
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
Posted Monday, September 24, 2012 4:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
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
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: 2 days ago @ 11:18 AM
Points: 380, Visits: 903
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
Posted Monday, September 24, 2012 4:55 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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
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: 2 days ago @ 11:18 AM
Points: 380, Visits: 903
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
Posted Tuesday, September 25, 2012 9:26 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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
Posted Tuesday, September 25, 2012 10:30 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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
Posted Tuesday, September 25, 2012 10:44 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:26 PM
Points: 2,330, Visits: 3,509
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1364180
Posted Tuesday, September 25, 2012 10:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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