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

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.

From stackoverflow

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

  Tomalak : Beat me to it. ;-) +1

  Smashery : Beat me too ;-) You can increase the readability of your post by indenting each line by 4 spaces (highlighting it and pressing the code sample button does this for you)

  Dead account : -1, he asked for a version without "Not in"

  onedaywhen : @Ian Quigley: the posted SQL doesn't use NOT IN, rather it uses NOT EXISTS.

  Dead account : +1 I looked into it, and Not exists is just as quick. Deleted my answer. My Bad!

• 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

• 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
  

• 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