Re: Cartesian outer join with plus-sign syntax

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 18 Dec 2007 22:04:28 -0800
Message-ID: <1198044252.966589@bubbleator.drizzle.com>

Jaap W. van Dijk wrote:
> On Mon, 17 Dec 2007 22:32:17 -0800, DA Morgan <damorgan_at_psoug.org>
> wrote:
>

>> Jaap W. van Dijk wrote:
>>> Hi
>>>
>>> I want to perform a cartesian outer join with the old plus-sign
>>> syntax. So far I've come up with (and it works)
>>>
>>> SELECT ...
>>> FROM A,B
>>> WHERE
>>> case when A.X = A.X then 0 else 0 end =
>>> case when B.X (+) = B.X (+) then 0 else 0 end
>>>
>>> Both tables are referred to, table B with the plus-sign, so the outer
>>> join requirement is satisfied. The condition is always true, so the
>>> join is in effect cartesian.
>>>
>>> Is there another (simpler) way to do this?
>>>
>>> Regards,
>>>
>>> Jaap.
>> There is no such thing as a CARTESIAN OUTER JOIN so define your terms
>> and your version. Are you talking about a FULL JOIN?
>> Morgan's Library (www.psoug.org) - look up Joins
>>
>> If so this is NOT the meaning of what is referred to as a CARTESIAN.
>> -- 
>> Daniel A. Morgan
>> Oracle Ace Director & Instructor
>> University of Washington
>> damorgan_at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org

>
> 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.

You still don't understand the concept. Here's a cartesian:

SELECT a.col1, b.col1
FROM tab1 a, tab2 b;

No WHERE clause ... every row in tab1 joined with every row in tab2.

That is not what you want. What you want is a FULL JOIN and it is covered on the Joins page in Morgan's Library.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Wed Dec 19 2007 - 00:04:28 CST