Reputation:
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.
Upvotes: 10
Views: 26142
Reputation: 91376
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
Upvotes: 3
Reputation: 11148
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
Upvotes: 0
Reputation: 75764
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
Upvotes: 2
Reputation: 6447
SELECT TableB.con_number
FROM TableB
WHERE NOT EXISTS (SELECT 1
FROM TableA
WHERE TableA.con_number = TableB.con_number);
Upvotes: 19