SQL Server NOT IN Clause

I’m currently working on a project where we are importing data transactions on a daily basis and need to compare the imported file with the current file. One of my favorite SQL clauses is the NOT IN clause, which I have used many times to do comparison type queries. Quite often, this powerful clause will help to simplify a system so much that its almost unbelievable.

This is how it looks;

SELECT DISTINCT
 resort,
 account,
 seqno,
 amount,
 pcode,
 ptype,
 pdate
FROM dbo.revenueMaster_import
WHERE dbo.revenueMaster_import.rowKey NOT IN(
 SELECT DISTINCT
  dbo.revenueMaster_archive.rowKey
 FROM
  dbo.revenueMaster_archive)
ORDER BY dbo.revenueMaster_import.seqno asc

This query shows all of the distinct rows from dbo.revenueMaster_import that are NOT IN dbo.revenueMaster_archive. There are many ways you might need to compare data like this. I’ve created many systems that utilize this technique to help keep a systems table structure to a minimum and therefore reduce a systems complexity.

Happy memorial Weekend!