How to do intersect and except sql operators in access?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

There is a union operator to find items in either of 2 sets. Is there a way
to find items that are in both sets and another to find items in one set but
not in the other?

A union B contains all rows from A plus all rows from B (with no duplicates)

A intersect B would contain only rows that are in both tables A and B

A except B would contain only rows that are in A but not in B
 
There is a union operator to find items in either of 2 sets. Is there a way
to find items that are in both sets and another to find items in one set but
not in the other?

A union B contains all rows from A plus all rows from B (with no duplicates)

A intersect B would contain only rows that are in both tables A and B
Click to expand...

A simple JOIN will accomplish this.
A except B would contain only rows that are in A but not in B
Click to expand...

Use a "frustrated outer join" -

SELECT A.*
FROM [A] LEFT JOIN
ON [A].[fieldname] = B.[fieldname]
WHERE .[fieldname] IS NULL;

John W. Vinson[MVP]
 
To find items that are in both sets, include both A and B in a query and
join A to B on the field you want in both sets.

To find items in one set but not in the other, open to the database window
and click on the queries tab. Click on new, select the unmatched query
option and follow the directions.
 
Thanks,

I missed the unmatched query builder, but already came up with this...

SELECT AllStreets.StreetCity INTO NewStreets
FROM AllStreets
WHERE (((AllStreets.StreetCity) Not In (select StreetCity from
MarkedStreets)));

which may not be the best performer.

Other SQL (IBM's DB2) engines support UNION, INTERSECT and EXCEPT to do the
SET operations. They are missing from Access.
 
Access supports union queries. For the other two, Access just uses different
names.
 
Back
Top