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

Swagerty

EXCEPT is the ANSI/ISO SQL term for MINUS. – Jackanapes

Answer 1

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.

Legionnaire

Why so? The logic produces the same results as the SQL standard INTERSECT and MINUS. – Legionnaire

This is exactly what I was saying and I got voted down. Ah well. +1 for you, since you took the time to actually give syntax examples, though. – Lubric

I'm not redefining any standards. MS Access does not use the standard. – Legionnaire

But this still only works properly if "ID" is a candidate key table a. – Telium

You need to have something to compare against, regardless, Dave. Can you elaborate? – Lubric

Also, Jason, shouldn't your answer use A.* instead of just *? – Lubric

Answer 2

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.

Telium

Cheers, forgot to add the distinct in my post. +1. – Legionnaire

Answer 3

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.

Lubric

Answer 4

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.

Argueta

Your MINUS implementation is much too convoluted. -1 – Legionnaire

It's not Access that doesn't support it. If you write a passthrough to SQL server using MINUS or INTERSECT it will work just fine. It's the Jet database engine whose SQL dialect does not these two operators. All SQL dialects are incomplete so this isn't some huge problem with Jet. – Eligibility

Answer 5

No MINUS in Access, but you can use a subquery.

SELECT DISTINCT a.*
FROM a
WHERE a.PK NOT IN (SELECT DISTINCT b.pk FROM b)

Thacher

Answer 6

I believe this one does the MINUS

SELECT DISTINCT
  a.CustomerID, 
  b.CustomerID
FROM 
  tblCustomers a
LEFT JOIN 
  [Copy Of tblCustomers] b
ON
  a.CustomerID = b.CustomerID
WHERE
  b.CustomerID IS NULL

Yean

how is this answer different from the others? – Chou

Actually, I tried the other answers, and I felt compelled to share that it works. – Yean

then you can then just wait to be able to upvote and upvote it later – Chou