SQL to find record with a value in one table but not another table

Question

I have two tables: Address and Address_Backup. Each table can contain multiple rows of data for a single individual because there are multiple address types stored in this table. So the data looks something like this:

ID    Code   Description  Description2  City   State
6798  HOME   478 Elm      NULL          Boise  ID
6798  OTHER  405 S Main   NULL          NULL   NULL

Address_Backup is supposed to be identical to Address, but we've found some data that exists in Address_Backup that doesn't exist in Address. I need a query that joins the ID numbers in the two tables and returns data for addresses where the "Other" code type exists in the Address_Backup table but does not exist in the Address table.

Answer 1

Assuming your IDs do not change in either table so that ID 100 in Address always points to ID 100 in Address_Backup:

  SELECT * 
    FROM Address_Backup
     WHERE ID NOT IN (SELECT ID FROM Address) AND Code = 'OTHER'

using NOT EXISTS

 SELECT * 
  FROM Address_Backup AS B
  WHERE NOT EXISTS (SELECT 1 FROM Address WHERE ID = B.ID) AND Code = 'OTHER'