# Re: Cartesian outer join with plus-sign syntax

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