finding distinct data between two tables

• David Greene-246981

  SSC Journeyman

  Points: 85

  More actions

  March 20, 2007 at 2:08 pm

  #176530

  I need to compare some data between two tables and find the data in one table that is not in the other.

  Here's the scenario:

  Table1 has 1500 records.

  Table2 has 1000 records.

  I need to find which records from Table2 are in Table1, but the data I want returned is which records are in Table1 but not in Table2.

  I have no idea how to write a comparison between the two and spit out the difference.

  Thanks for your help,

  David

• mrpolecat

  SSCertifiable

  Points: 7014

  More actions

  March 20, 2007 at 2:53 pm

  #696049

  select table1.* from table1 left join table2 on table1.pk = table2.pk where table2.pk is null

  pk is whatever field is common between the 2 tables

  ---

• James Moore (Red Gate)

  SSCrazy

  Points: 2644

  More actions

  March 20, 2007 at 2:55 pm

  #696050

  Hi David,

  One way to do this is to do a left join and then check for null on the right handle columns, for example

  SELECT Table1.* FROM

  Table1

  LEFT JOIN

  Table2

  ON Table1.ID = Table2.ID

  WHERE Table2.ID IS NULL

  This query will return all of the records in Table1 which are not in table2. You can reverse it (or do a full join) to get records which exists in either Table1 or Table2 but not both.

  Red Gate also do a tool called SQL Data Compare which will compare data in two tables tell you which records differ and generate a synchronisation script automatically for you.

  Hope this helps,

  - James

  --
  James Moore
  Red Gate Software Ltd

• David Greene-246981

  SSC Journeyman

  Points: 85

  More actions

  March 21, 2007 at 7:34 am

  #696141

  PERFECT guys!

  Thanks so much.