SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Should i use NOT IN table A and table B
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

t0ze
Starting Member

10 Posts

Posted - 12/30/2010 :  07:45:54  Show Profile  Reply with Quote
I have this scenario:

TABLE1
ID | Title
1 | Hello Earth
2 | Hello MArs
3 | Hello Moon

And TABLE2
ID | ID_Table1 | Date
1 | 1 | 12-12-1999
1 | 1 | 21-12-1999
1 | 2 | 13-12-1999

The result should be
TABLE1
ID | Title
3 | Hello Moon

With no success i'm using something like this

SELECT TABLE1.*
FROM TABLE1
WHERE TABLE1.ID NOT IN (SELECT TABLE2.ID_Table1
FROM TABLE2)


Edited by - t0ze on 12/30/2010 07:54:03

malpashaa
Constraint Violating Yak Guru

Saudi Arabia
257 Posts

Posted - 12/30/2010 :  08:45:27  Show Profile  Reply with Quote
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)
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/30/2010 :  11:56:40  Show Profile  Reply with Quote
Another alternative is to use a LEFT OUTER JOIN.
Go to Top of Page

t0ze
Starting Member

10 Posts

Posted - 12/30/2010 :  12:27:07  Show Profile  Reply with Quote
Tks Lamprey and malpashaa

It's solved :))))

Best wishes for 2011

Edited by - t0ze on 12/30/2010 12:27:27
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/30/2010 :  13:42:44  Show Profile  Reply with Quote
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)
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/30/2010 :  14:00:48  Show Profile  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/30/2010 :  14:17:06  Show Profile  Reply with Quote
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

Edited by - Kristen on 12/30/2010 14:17:27
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000