RE: IN / NOT IN differences

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Mal .mullerjannie_at_hotmail.com> (.mullerjannie_at_hotmail.com)
Date: 10/07/04 

Date: Thu, 7 Oct 2004 04:19:03 -0700

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

Relevant Pages

  • RE: IN / NOT IN differences
    ... chunk the tables in smaller pieces to try to post an example. ... working with SQL Server for 7 years and I have never seen this issue. ... > right outer join table2 ... >> Two tables with IDs ...
    (microsoft.public.sqlserver.programming)
  • RE: IN / NOT IN differences
    ... chunk the tables in smaller pieces to try to post an example. ... working with SQL Server for 7 years and I have never seen this issue. ... > right outer join table2 ... >> Two tables with IDs ...
    (microsoft.public.sqlserver.programming)
  • Re: Outer join by (+)
    ... from table1 left outer join table2 on ... from table1, table2 ... I'd generally prefer the OUTER JOIN syntax as it is more clear and ...
    (comp.databases.oracle.server)
  • Re: Major query performance problem...
    ... your best solution may be to add a computed column to Table1 ... from Table1 left outer join Table2 ... I wrote an SQL Statement but found a major, ...
    (microsoft.public.sqlserver.programming)
  • RE: Help me with this Select Statement
    ... FROM Table1 T1 ... LEFT OUTER JOIN Table2 T2 ... "pvong" wrote: ... Buy and Sell where ClientID = @ClientID. ...
    (microsoft.public.sqlserver.programming)