How do I get all the rows in one table that are not in another in MS Access?

Question

I have tried a bunch of different things but always get syntax errors.

I have two tables - tableA and tableB. They both have a con_number field as a unique ID. I want to select all of the rows in tableB that do not exist in tableA.

Can anyone please give me this query as it would be in MS Access?

I know that using NOT IN is quite inefficient in this case so if there is a better way then that would be great.

Thanks.

Answer 1

SELECT TableB.con_number
FROM TableB
WHERE NOT EXISTS (SELECT 1 
                  FROM TableA 
                  WHERE TableA.con_number = TableB.con_number);

Answer 2

NOT IN version (slow but sure):

SELECT con_number
FROM TableB
WHERE con_number NOT IN (SELECT con_number FROM tableA);

experimental version (don't know if this is any faster, just try it out):

SELECT B.con_number, MAX(A.con_number) AS check
FROM tableB B LEFT JOIN tableA A ON B.con_number = A.con_number
GROUP BY B.con_number
HAVING check IS NULL;

Note: Both should be fairly standard SQL, I don't know any ms-access specific features

Answer 3

I remember something like this one:

SELECT * FROM TableA.* LEFT JOIN TableB _
    ON TableA.con_number = TableB.con_number WHERE 'criteria'

But I don't remember which 'criteria' to use

... TableA.con_number <> TableB.con_Number
... TableB.con_number IS NULL
... TableA.con_number NOT like TableB.con_Number

Answer 4

There is a Find Unmatched wizard that will set this up. The SQL is:

SELECT TableB.con_number
FROM TableB LEFT JOIN TableA 
ON TableB.con_number = TableA.con_number
WHERE TableA.con_number Is Null