Re: Cartesian outer join with plus-sign syntax

On Jan 2, 12:17 pm, j.w.vandijk.removet..._at_hetnet.nl (Jaap W. van Dijk) wrote:
> On Wed, 26 Dec 2007 20:03:58 -0800 (PST), Arun Mathur
>
>
>
>
>
> <themath..._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.- Hide quoted text -
>
> - Show quoted text -

Thanks Jaap. Can you put together a sample script that shows the table structures and query that you're currently working with?

Regards,
Arun Received on Wed Jan 02 2008 - 15:55:11 CST