Strange behavior with "where not in" clause

Question

Strange behavior with "where not in" clause

digimetic

SSC Veteran

Points: 253
More actions
August 2, 2012 at 8:52 am

#279046

hi,
i've two tables that i'm comparing to generate a difference set. in the past, i've used a "not in" clause to compare attributes since it seems to outperform left joins where the left is null. Anyway, i've a case where this type of select statement is returning 0 rows in a delta set even though there clearly is such a set. Further, running the same select using a left join where left is null actually returns the correct result.
e.g.
select distinct t1.keyVal
from t1
where t1.keyVal not in (select t2.keyVal from t2);
this statement is returning 0 rows even though there are keys in t1 that do not exist in t2.
however, this works:
select distinct t1.keyVal
from t1
left join t2 on t1.keyVal = t2.keyVal
where t2.keyVal is null
what's even more baffling is that i swear the first query was working the other days during tests!
thoughts? thanks,
- b

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Any rows with NULL in keyVal in T2? If so, Not In will return 0 rows.

Makes sense if you think of NULL as "we don't know". "Not in" means "definitely no matches", and if what you're matching it against is unknown, then "definitely" fails.

Like asking, "is 7 = X?" Well, if you don't know the value assigned to X, it might be.

(Edit to correct a typo.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Heh you beat me to it, Gus.

Gail has a great blog covering this behaviour here[/url].

^{“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

digimetic SSC Veteran Points: 253 More actions · Answer 3

bingo...

i'd forgotten there was a scrubbing statement that removes rows from the comparison table where the keys were null.

thanks for dope slapping me 😀

- b