Re: compound SQL queries and DISTINCT problem

For the minus problem, a null outer join is the best choice:

SELECT <blah> from a, b WHERE a.id = b.id (+) AND b.id IS NULL;

For the intersect problem, use a direct join.

Good Luck,
Chris Weiss

"Sten Loecher" <sten-loecher_at_gmx.de> wrote in message news:9f2i45$qu6$1_at_kastor.inf.tu-dresden.de...
> Hi folks,
>
> I need to write compound queries like this:
>
> select * from some_table1
> minus
> select * from some_table2
>
> or:
>
> select * from some_table1
> intersect
> select * from some_table2
>
> The problem is, that I need all rows from some_table1 or some_table2
> respectivly. However, the minus and intersect operator returns only
 distinct
> rows from the according tables. The SQL standard allows for example
> INTERSECT ALL. Oracle do not provide this feature. Does anyone have an
 idea
> to get all rows without using predicates in the where clause of the
> statements ?
>
> Would appreciate any help.
>
> Sten
>
>
Received on Tue Jun 05 2001 - 14:13:31 CDT