# Re: Cartesian outer join with plus-sign syntax

Date: Wed, 26 Dec 2007 20:03:58 -0800 (PST)

Message-ID: <dcf3ac74-555e-4fa6-9927-1cf9e3f5736f@a35g2000prf.googlegroups.com>

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
Received on Wed Dec 26 2007 - 22:03:58 CST