Saturday, 10 November 2012

Finding records missing in target table

Often there is a need to find records which exist in source table but not in target table.
This can be achieved with simple SQL statement:

SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE t2.Id = t1.Id)

or

SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.Id = t2.Id WHERE t2.Id IS NULL

This works not only for SQL Server, but also for many other databases.