How can I implement SQL INTERSECT and MINUS operations in MS Access

Question

I have researched and haven't found a way to run INTERSECT and MINUS operations in MS Access. Does any way exist

Answer 1

They're done through JOINs. The old fashioned way :)

For INTERSECT, you can use an INNER JOIN. Pretty straightforward. Just need to use a GROUP BY or DISTINCT if you have don't have a pure one-to-one relationship going on. Otherwise, as others had mentioned, you can get more results than you'd expect.

For MINUS, you can use a LEFT JOIN and use the WHERE to limit it so you're only getting back rows from your main table that don't have a match with the LEFT JOINed table.

Easy peasy.

Answer 2

INTERSECT is an inner join. MINUS is an outer join, where you choose only the records that don't exist in the other table.

---

INTERSECT

select distinct
  a.*
from
  a
  inner join b on a.id = b.id

---

MINUS

select distinct
  a.*
from
  a
  left outer join b on a.id = b.id
where
  b.id is null

If you edit your original question and post some sample data then an example can be given.

EDIT: Forgot to add in the distinct to the queries.

Answer 3

Unfortunately MINUS is not supported in MS Access - one workaround would be to create three queries, one with the full dataset, one that pulls the rows you want to filter out, and a third that left joins the two tables and only pulls records that only exist in your full dataset.

Same thing goes for INTERSECT, except you would be doing it via an inner join and only returning records that exist in both.

Answer 4

INTERSECT is NOT an INNER JOIN. They're different. An INNER JOIN will give you duplicate rows in cases where INTERSECT WILL not. You can get equivalent results by:

SELECT DISTINCT a.*
FROM a
INNER JOIN b
   on a.PK = b.PK

Note that PK must be the primary key column or columns. If there is no PK on the table (BAD!), you must write it like so:

SELECT DISTINCT a.*
FROM a
INNER JOIN b
   ON  a.Col1 = b.Col1
   AND a.Col2 = b.Col2
   AND a.Col3 = b.Col3 ...

With MINUS, you can do the same thing, but with a LEFT JOIN, and a WHERE condition checking for null on one of table b's non-nullable columns (preferably the primary key).

SELECT DISTINCT a.*
FROM a
LEFT JOIN b
   on a.PK = b.PK
WHERE b.PK IS NULL

That should do it.

Answer 5

i still got a problem with that topic: i have to INTERSECT several Queries on one Table. Which ON-Statements do I have to use? I can't type "ON Table.ID = Table.ID"

Answer 6

MDBScript is the best tool to execute SQL script on your MS Access database. Check out its website: http://www.mdbscript.com/