How Can I Replicate SQL INTERSECT and MINUS in MS Access?

Implementing SQL INTERSECT and MINUS Operations in MS Access

In the absence of native support for SQL INTERSECT and MINUS operations in MS Access, creative solutions can be crafted using standard SQL syntax.

INTERSECT (Inner Join)

The INTERSECT operation extracts records that exist in both tables being compared. To achieve this in Access, employ an inner join:

SELECT DISTINCT
  a.*
FROM
  a
INNER JOIN b ON a.id = b.id

MINUS (Outer Join)

The MINUS operation returns records that exist in the first table but not in the second. Implement this using an outer join and filter out records with matching IDs:

SELECT DISTINCT
  a.*
FROM
  a
LEFT OUTER JOIN b ON a.id = b.id
WHERE
  b.id IS NULL

Example

Consider tables a and b with the following data:

a.id

1

2

3

b.id

1

4

INTERSECT

id

1

MINUS

id

2

3

The above is the detailed content of How Can I Replicate SQL INTERSECT and MINUS in MS Access?. For more information, please follow other related articles on the PHP Chinese website!