Should i use NOT IN table A and table B

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Should i use NOT IN table A and table B

Author

Topic

t0ze
Starting Member

10 Posts

Posted - 2010-12-30 : 07:45:54

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-12-30 : 08:45:27

You should use NOT EXISTS instead, as NOT IN will affected by null values in ID_Table1.


SELECT T1.*
  FROM TABLE1 AS T1
 WHERE NOT EXISTS(SELECT *
                    FROM TABLE2 AS T2
                   WHERE T2.ID_Table1 = T1.ID)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-30 : 11:56:40

Another alternative is to use a LEFT OUTER JOIN.

t0ze
Starting Member

10 Posts

Posted - 2010-12-30 : 12:27:07

Tks Lamprey and malpashaa

It's solved :))))

Best wishes for 2011

Kristen
Test

22859 Posts

Posted - 2010-12-30 : 13:42:44

quote:
Originally posted by Lamprey

Another alternative is to use a LEFT OUTER JOIN.

That's usually (99%!!) my preferred solution, but I always hesitate to recommend it as I think it may be perceived of as "esoteric" - what do you think? (I expect it may be undesirable too if the Second Table has multiple hits for the JOIN criteria)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-30 : 14:00:48

quote:
Originally posted by Kristen

quote:
Originally posted by Lamprey

Another alternative is to use a LEFT OUTER JOIN.

That's usually (99%!!) my preferred solution, but I always hesitate to recommend it as I think it may be perceived of as "esoteric" - what do you think? (I expect it may be undesirable too if the Second Table has multiple hits for the JOIN criteria)

Depends.. :)

I agree though, I almost always use an outer join for this sort of thing. Other people have done perf tests and claim that not exists is faster than an outer join. I'm not 100% sure what tests they ran. But, I almost always find an outer join to be more performant for what I do and on my data sets. Although, it depends on what you are doing, the data and the indexes as to which will work better.

There can also be personal preference. I don't like correlated sub-queries, not exists predicates and the like, so I tend to do joins for everything and then tweak for performance from there. Every so often, a NOT EXISTS will be faster/less resource intensive than an outer join so I use it.

Kristen
Test

22859 Posts

Posted - 2010-12-30 : 14:17:06

My main reason for preferring OUTER JOIN is that I often have to re-use the code for something else where changing to an INNER JOIN (say) is much easier than converting an EIXSTS to a JOIN!


-- Source record removed
DELETE D
FROM MyTable AS D
    LEFT OUTER JOIN OtherTable AS S
        ON S.ID = D.ID
WHERE S.ID IS NULL

-- Source record changed
UPDATE D
SET Col1 = S.Col1
FROM MyTable AS D
    JOIN OtherTable AS S
        ON S.ID = D.ID
WHERE S.Col1 <> D.Col1

-- New record added to Source
INSERT MyTable(Col1, Col2, ...)
SELECT Col1, Col2, ...
FROM OtherTable AS S
    LEFT OUTER JOIN MyTable AS D
        ON D.ID = S.ID
WHERE D.ID IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources