Re: Cartesian outer join with plus-sign syntax

On Dec 18, 12:14 pm, j.w.vandijk.removet..._at_hetnet.nl (Jaap W. van Dijk) wrote:

> What I meant was: I want to perform a cartesian join of two tables A
> and B (no join conditions between columns of the two tables).
>
> But: if table B is empty I still want to see all the records of table
> A, with the columns of table B in the select-list filled with null.
>
> The latter may not be a outer join in the strict sense, I don't know,
> but it looks a lot like it, so I called the combination cartesian
> outer join.
>
> A regular cartesian join yields no records if one of the tables is
> empty, so I did some thinking and fiddling and found a solution that
> yields what I want, but I wondered if there are other solutions,
> because the problem seems so simple and my solution so cumbersome.
>
> The version in which I tried tried this is 9.2.0.5.
>
> Regards, Jaap.

Hi Jaap,

It looks like a full outer join (available in 9.2.0.5, IIRC) should work?

SQL> create table a(c number);

Table created.

SQL> create table b(c number);

Table created.

SQL> insert into a select rownum from dba_objects where rownum < 10;

9 rows created.

SQL> commit;

Commit complete.

SQL> select a.c,b.c from a full outer join b on b.c=a.c   2 /

         C C
---------- ----------

         5
         8
         3
         1
         2
         6
         7
         4
         9

9 rows selected.

SQL> Regards,

Steve Received on Tue Dec 18 2007 - 11:33:58 CST