SQL Intersect

  • Thread starter Thread starter Allan
  • Start date Start date
A

Allan

I am going through a "teach yourself SQL in 21 days" and doing the examples
in Access. They are using Personal Oracle in the book, so I am noting the
differences in implementations between Jet SQL (Access) and Personal Oracle.

My Question:
HOW DO WE ACCOMPLISH THE "INTERSECT" OPERATOR in MS Jet SQL?

============================
Allan
 
Hi,

I don't know the implementation of Intersect, in ORACLE, but in general, an
intersection can be represented with an INNER JOIN (if there is no
duplicated values) or with an IN criteria.



SELECT Table1.*
FROM Table1 INNER JOIN table2
ON table1.pk=table2.pk


would return records in table1 having their primary key in table2. You can
also try:

SELECT *
FROM Table1
WHERE pk IN(SELECT pk FROM table2)



The first formulation won't work with involved fields in table2 that can
have duplicated values. If table2.pk has two records with the same value,
and the value is also present in table1.pk, then the result would present
the record from table1, but twice.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top