Compare Two SQL identical Table to find missing records

Question

I am working on SQL 2008. I have two identical tables with same column names.

On Table2, i am missing some records. Some records got deleted in the Table2.

I have to compare Table1 and Table2 and retrieve only missing records from table1.

Answer 1

Use a LEFT JOIN and check for IS NULL like below. where t2.col2 is null will be TRUE only when there are records in table1 which are not present in table2. Which is what you are looking for. [This is a sample code and have no resemblance with your original query]

select t1.col1,t1.col2,t1.col3
from table1 t1
left join table2 t2 on t1.some_column = t2.some_column
where t2.col2 is null

Answer 2

You should use SQL Except. There is no Join involved.

Select * from dbo.TableA
Except
Select * from dbo.TableB

In set theory, the difference of sets A, B (A-B) is the set of elements that belong to A and do not belong to B.

Answer 3

With an " not exists", you have a solution :

select * from Table1 t1 where not exists (select 1 from Table2 t2  
    where t1.col1 = t2.col1
      and t1.col2 = t2.col2  
      and t1.col3 = t2.col3  
      and ...                 // check here all columns ...
   ) 

There is however a little problem in this solution in the case of null values, which can only be tested via a "IS NOT NULL" or "IS NULL", hence the complementary solution:

select * from Table1 t1 where not exists (select 1 from Table2 t2  
    where (t1.col1 = t2.col1 or (t1.col1 IS NULL and t2.col1 IS NULL))
      and (t1.col2 = t2.col2 or (t1.col2 IS NULL and t2.col2 IS NULL))  
      and (t1.col3 = t2.col3 or (t1.col3 IS NULL and t2.col3 IS NULL))  
      and ...                 // check here all columns ...
   )