RE: IN / NOT IN differences
From: Salvador (Salvador_at_discussions.microsoft.com)
Date: 10/07/04
- Next message: Thomas Steinmaurer: "Re: trigger each row"
- Previous message: J T: "Re: Non Clustered Index makes sense but Clustered Index does not."
- In reply to: Mal .mullerjannie_at_hotmail.com>: "RE: IN / NOT IN differences"
- Next in thread: Salvador: "RE: IN / NOT IN differences"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 7 Oct 2004 05:07:03 -0700
Hi,
I have checked and there are not NULL, also none duplicates, I am trying to
chunk the tables in smaller pieces to try to post an example. I have been
working with SQL Server for 7 years and I have never seen this issue. So I am
copying this tables to another server to check if there is something wrong
with my one. As soon as I get an example I will write you back.
I tried with a Join and is working fine, but the real issue is I want to
know why is not working with the NOT IN clause.
Thanks a lot,
Salvador
"Mal" wrote:
> I dont know your data so I can't really give accurate answer, but I would say
> it's because of NULLS. They always cause unexplained results that we don't
> think about..
>
> Use a join,
>
> --to get values from table 1 not in table 2
> Select ID from table1
> left outer join table2
> on table1.id = table2.id
> where table2.id is null
>
> --to get values from table 2 not in table 1
> Select ID from table1
> right outer join table2
> on table1.id = table2.id
> where table1.id is null
>
> Don't know if it's imagination but I find joins alot faster.
>
>
> "Salvador" wrote:
>
> > Hi All,
> >
> > Just a quick question if anybody knows what I am doing wrong, this never
> > happened to me before with the IN / NOT IN clause
> >
> > Scenario:
> > Two tables with IDs
> > Table1 = 180000 rows
> > Table2 = 50000 rows
> > SQL Server 2000 8.00.760 (Win2K SP4)
> >
> > Problem:
> > Want to find which IDs are not in the other table
> >
> > Query:
> > SELECT ID FROM Table1 WHERE ID NOT IN (SELECT ID FROM Table2)
> > Results = 0
> > SELECT ID FROM Table1 WHERE ID IN (SELECT ID FROM Table2)
> > Results = 40000
> > SELECT ID FROM Table2 WHERE ID NOT IN (SELECT ID FROM Table1)
> > Results = 10000
> > SELECT ID FROM Table2 WHERE ID IN (SELECT ID FROM Table1)
> > Results = 40000
> >
> > How is possible that the first query returns 0 ? it should be 140000 or I am
> > doing something wrong?
> >
> > Same data types, I have tried casting as well
> > Same collation.
> >
> > Any Clues? Any idea of any bug reported?
> > Thanks a lot
> > Salvador
- Next message: Thomas Steinmaurer: "Re: trigger each row"
- Previous message: J T: "Re: Non Clustered Index makes sense but Clustered Index does not."
- In reply to: Mal .mullerjannie_at_hotmail.com>: "RE: IN / NOT IN differences"
- Next in thread: Salvador: "RE: IN / NOT IN differences"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
