Behavior of "NOT IN"
-
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?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
-
S_Kumar_S (9/24/2012)
HiHere 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[/url].
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
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)
HiHere 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?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thats a nice explanation. I got your point Gail. Thank you...
GilaMonster (9/24/2012)
Add OR nm IS NULLNull 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.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
-
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
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply