Re: Cartesian outer join with plus-sign syntax

On Tue, 18 Dec 2007 09:33:58 -0800 (PST), Steve Howard <stevedhoward_at_gmail.com> wrote:

>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

Yes, if table b is empty, the full outer join gives the desired result. But let's now look at the case that table b is filled with one record where c = 10. A full outer join will result in

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

         5
         8
         3
         1
         2
         6
         7
         4
         9
                   10

but what I want is a cartesian join, so the result should be

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

         5         10
         8         10
         3         10
         1         10
         2         10
         6         10
         7         10
         4         10
         9         10

I think the term I used, cartesian outer join, is appropriate, because what I want is an outer join with no join condition. This cannot be coded as such, because the ON-clause is mandatory.

While typing this I came up with the idea of putting "1=1" in the ON-clause of the full outer join. This works when table B is not empty, but when table B is empty this results in zero rows.

I this correct I wonder? I would have expected Oracle to keep track of the number of records from B that could be found for a record in A, and if this number is zero, to show this record from A with the columns of B set to null. But Oracle seems to check if the join condition is true for any record of B. When B is empty this check still comes up positive (because he condition is true irrespective of B being empty or not), and if so Oracle combines every record of A with records of B that satisfy the condition. Because B is empty, zero records result.

Regards, Jaap. Received on Sun Dec 23 2007 - 17:50:12 CST