Re: Cartesian outer join with plus-sign syntax

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. Received on Tue Dec 18 2007 - 11:14:30 CST