Re: Non-equal Joins

Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de> wrote:
>Hi John,
>
>You are right. The only way to avoid that a cartesian product
 has to be
>considered is an equi-join.
>
>Martin
>
>
>John Jones wrote:
>>
>> I seem to remember somewhere in the dark recesses of my brain
 that a
>> NON-equal join would cause a product. Can any one confirm
 this or
>> is old age setting in. Please respond to john.jones_at_duke.edu
 if possbile.
>> Don't always get a chance to read news groups. Thanks.
>>
>> --
>> John Jones
>> Senior Oracle DBA
>> Duke University OIT
>> john.jones_at_duke.edu
>

Martain I am not sure I understand your reply. I believe a cartesian product is where every row in A is joined to every row in B such as in

select a.*, b.*
from A, B ;

Since there is no where clause every row of A is joined to every row of B with the result set containing the number of rows in A multiplied by the number of rows in B.

If I add a where clause such that A.col1 = B.col1 then I get back a result set which contains only those rows that meet the condition. If I then change the equality to another relational operator like greater than, >, I still get back only the subset of the data that meets my condition. I do not see how this qualifies as a cartesian product.

Though if there are two columns that are required to associate the related rows in two tables and I specifiy only one in the join condition then my result set contains a partial cartesian in the sence I have unwanted and incorrectly associated data.

Please provide an fuller explanation to your post as I seemed to have missed some of the underlying theory.

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Sat Jul 22 2000 - 00:00:00 CDT