# Re: Cartesian outer join with plus-sign syntax

From: Jaap W. van Dijk <j.w.vandijk.removethis_at_hetnet.nl>
Date: Wed, 02 Jan 2008 17:17:44 GMT
Message-ID: <477bc622.12958937@news.hetnet.nl>

On Wed, 26 Dec 2007 20:03:58 -0800 (PST), Arun Mathur <themathurs_at_gmail.com> wrote:

>On Dec 23, 6:50 pm, j.w.vandijk.removet..._at_hetnet.nl (Jaap W. van
>Dijk) wrote:
>> On Tue, 18 Dec 2007 09:33:58 -0800 (PST), Steve Howard
>>
>>
>>
>> <stevedhow..._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.
>
>Hi Jaap,
>
>It sounds like the number of rows in table B determines which
>resultset should be returned ie cartesian product or full outer join.
>If so, what if you were to put together a PL/SQL function that returns
>a ref cursor? The function would first check if any rows in B exist.
>If B is empty, return a refcursor based on a full outer join
>operation. If B is not empty, return a refcursor based on a cartesian
>product.
>
>Regards,
>Arun
>
>

Hi Arun,

If you check the post that started this thread, you will find that I started this thread with a working solution, so that's not the problem. I just wondered if there were simpler solution than mine, and I think yours is more elaborate.

Regards, Jaap. Received on Wed Jan 02 2008 - 11:17:44 CST

Original text of this message