• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

How to select records that are in one database but not the other

I have two tables with some data. They each share a common characteristic (a customer id field).
I would like to create a SQL statement that would select everything from table A that is not in table B.
For example if table A has the id's 1,2,3,4 and table B has the values of 1,4; I only want to get rows 2 and 3
from table A out of the query. I have looked at an inner join but it seems to only do the opposite of what I want...
How do I get distinct records from table A that are NOT in table B?
Thank's in advance.
0
jpwallen
Asked:
jpwallen
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it will be a variant of the LEFT OUTER JOIN. the trick is the WHERE clause:
SELECT a.*
  FROM tableA a
  LEFT OUTER JOIN tableB b
    ON a.id = b.id
 WHERE b.id IS NULL

Open in new window

0
 
albuitraCommented:
select * from table A where not exists (select b.* from b where a.field = b.field )
0
 
knowledge_riotCommented:
You could also try:

SELECT DISTINCT CustomerId from TableA where CustomerId not in (SELECT CustomerId from TableB)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now