Ms-access – Exclude table’s data from another table

ms accessms-access-2010

Is it possible to create a query and ask the following?
I have two tables (each one has one column with data) and I want to obtain as a result all data from the first table MINUS the data from the second table.

Best Answer

In standard SQL you could do that very easily with EXCEPT:

SELECT
  Column1
FROM
  Table1

EXCEPT

SELECT
  Column2
FROM
  Table2;

However, EXCEPT is unsupported in MS Access. Instead, you could use an anti-join. There are various ways of implementing an anti-join. One is using an EXISTS predicate:

SELECT
  Column1
FROM
  Table1 AS t1
WHERE
  NOT EXISTS
  (
    SELECT
      *
    FROM
      Table2 AS t2
    WHERE
      t1.Column1 = t2.Column2
  );
Related Question