Querying Two Tables For Duplicate Values

Question

I have a stored procedure which moves (or ought to) records from one table to another. However I now want to create a procedure that checks the data in 'TableA' against the data in 'TableB'

SELECT A.Num, B.Num
FROM TableA AS A 
LEFT JOIN TableB AS B ON A.Num = B.Num
WHERE B.Num IS NULL

Basically, I want to pull out any number which isn't in 'TableB' but is in 'TableA', is a LEFT JOIN the way to do this? I have been unsuccessful in locating missing files so far, and I have removed some to form a test case.

Answer 1

You can use a not exists

SELECT  *   
FROM TableA A  
WHERE NOT EXISTS (SELECT *      
                  FROM TableB B
                  WHERE A.NUM = B.NUM);

or not in:

SELECT  *   
FROM TableA A  
WHERE A.NUM not in (SELECT B.NUM
                  FROM TableB B);

Answer 2

SELECT Num from TableA
EXCEPT
SELECT Num from TableB