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

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 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.