December 27, 2010 at 1:03 pm
Greetings SSC!
I looked all over and can't find a solution. Maybe the people here have some experience with this kind of issue.
I have two tables. Table A has 36679 records and Table B has 36800 records. I want to get the mismatch records from Table B when compared to Table A.
When I execute the following SQL, I get 0 records:
select * from TableB where fname not in (select fname2 from TableA)
Now, when I take a subset of these records and run the same code, it returns the differences. For example:
TableA: A3,A5,A6
TableB, A1,A2,A3,A4,A5,A6
When I compare these, I get the expected 3 rows. If I use the superset, I get 0.
The only apparent difference is the number of records in each table. Surely I can use NOT IN with a mere 37k records?
Note: I also copied the tables via SELECT * INTO TableA/SELECT * INTO TableB and still got 0 records. I get the same results in 2000 and 2008.
Thanks in advance for any help you can offer and thanks to all the professionals that help us newbies out!
Rick
December 27, 2010 at 1:14 pm
Change:
select * from TableB where fname not in (select fname2 from TableA)
To:
select * from TableB where fname not in (select fname2 from TableA where fname2 is not null)
See if that gets it for you.
Where Not In does not work the way you might think if there are Null values in the sub-query. Since Null means "unknown value", it could be anything, and thus "Not In" will return no rows, because it might be in there, if you knew what the Null was. If Null meant "no value", that would be different, but it standardly means "Unknown Value".
Think of it this way:
I have four numbers: 1, 2, 3, and You Don't Know.
Is 4 part of that, yes or no?
- 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
December 27, 2010 at 1:18 pm
Brilliant! That did the trick.
I did indeed have NULLs being returned and I should have known better. Thanks for the excellent explanation.
December 27, 2010 at 1:20 pm
You're welcome.
- 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
December 28, 2010 at 10:01 am
Don't feel bad - this is such a common misconception that I have it as a slide in a "Common TSQL Mistakes" presentation I have given at many SQL Saturday conferences! 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 29, 2010 at 9:23 am
A good reason to design nulls out of your database
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply