How to get all rows in one table that are not in another in MS Access?

Question

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

I have two tables - tableA and tableB. Both of them have a con_number field as a unique identifier. I want to select all rows in table B that are not in table A.

Can someone please give me this request, as it would be in MS Access?

I know that using NOT IN is pretty inefficient in this case, so if there is a better way, 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 the version (slowly but surely):

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

experimental version (I don’t know if it is faster, just try):

 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 pretty standard SQL, I don't know any specific ms-access functions

Answer 3

There is an Unmatched search wizard that will install this. SQL:

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

Answer 4

I remember something like this:

 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