How to Simulate SQL INTERSECT and MINUS in Microsoft Access?

Implementing SQL INTERSECT and MINUS in Microsoft Access

Although not readily apparent within Microsoft Access, it is possible to replicate the functionality of SQL's INTERSECT and MINUS operations.

INTERSECT

INTERSECT retrieves only the rows that are common to both joined tables. It acts as an inner join.

Query:

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

MINUS

MINUS retrieves rows from table 'a' that do not exist in table 'b'. This operation is similar to an outer join with a specific condition.

Query:

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

Example:

If you have sample data, you can modify these queries to retrieve the desired results. For instance, if your tables have columns like 'id', 'name', and 'age':

INTERSECT

SELECT DISTINCT
  a.id, a.name, a.age
FROM
  tblA AS a
INNER JOIN
  tblB AS b ON a.id = b.id;

MINUS

SELECT DISTINCT
  a.id, a.name, a.age
FROM
  tblA AS a
LEFT OUTER JOIN
  tblB AS b ON a.id = b.id
WHERE
  b.id IS NULL;

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