Re: Non-equal Joins

I think there is a lack of clear definition of "cartesian product".

You are right that the result set will not contain the full cartesian product, of course. If this were not the case then SQL would yield a wrong result which will never happen ;-)

But as you indicated the amount of data you have to *read* is in the order of magnitude of records you have to read when you do a full table scan.

The idea of "orders of magnitude" is the following. Imagine you would double your source data. Which factor of number of rows you would have to read to get the desired result (remember: I am not talking about the number of result rows!).

In an equijoin, if you double the size of your tables you will read twice as much rows altogether. Hence this is linear behaviour.

In a join by the operand ">" or "!=" you have to read four times the rows altogether to get your result. Hence this is quadratic behaviour.

So let us define a cartesian product for two tables to be something that has quadratic behaviour. This is mathematically more precise. In this sense only an equijoin behaves linearly, and, therefore, is not a cartesian product.

Martin Received on Sun Jul 23 2000 - 00:00:00 CDT