PDA

View Full Version : SQL Intersect


Allan
June 1st, 2004, 03:46 PM
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

Michel Walsh
June 1st, 2004, 05:08 PM
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



"Allan" > wrote in message
news:[email protected]
> 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
>
>

Google