Re: Cartesian outer join with plus-sign syntax

On Tue, 18 Dec 2007 22:04:28 -0800, DA Morgan <damorgan_at_psoug.org> wrote:

>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

I don't see what is different between your definition of a cartesian join:

>SELECT a.col1, b.col1
>FROM tab1 a, tab2 b;
>
>No WHERE clause ... every row in tab1 joined with every row in tab2.
>

and mine:

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

Regards, Jaap. Received on Sun Dec 23 2007 - 17:05:27 CST