Select pairs from one table not in another?

Question

Select pairs from one table not in another?

andy.gear

SSC-Addicted

Points: 493
More actions
June 10, 2009 at 7:37 am

#133150

So I have two tables that have two fields that are the same. How do I find pairs in one table that are not in another table? For Example:
Table1
Cust#, CustName
1,Cust1
2,Cust2
3,Cust3
Table2
Cust#,CustName
1,Cust1
4,Cust4
I want to write a query that compares Table1 to Table2 and results in:
4,Cust4

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

andy.gear (6/10/2009)
So I have two tables that have two fields that are the same. How do I find pairs in one table that are not in another table? For Example:
Table1
Cust#, CustName
1,Cust1
2,Cust2
3,Cust3
Table2
Cust#,CustName
1,Cust1
4,Cust4
I want to write a query that compares Table1 to Table2 and results in:
4,Cust4

a simple LEFT join should do it:

SELECT * FROM Table1

LEFT OUTER JOIN Table2

ON Table1.[Cust#] = Table2.[Cust#]

AND Table1.[CustName] = Table2.[CustName]

WHERE Table2.[CustName] IS NULL --not in Table2

--OR

--WHERE Table1.[CustName] IS NULL --not in Table1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 2

Using EXCEPT will give you pairs in Table1 that aren't in Table2

SELECT [Cust#],CustName FROM Table1

EXCEPT

SELECT [Cust#],CustName FROM Table2

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537