Re: Cartesian outer join with plus-sign syntax

From: Arun Mathur <themathurs_at_gmail.com>
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

Original text of this message