I appreciate your explanation. You may be correct about Standard SQL,
however I do not believe that this can be explained as a simple
cross-product. If a LEFT JOIN were implemented starting with a cross product
then NO LEFT JOIN would EVER return NULL in the right table. A pure cross
product would not take into consideration a non existant record in the right
table.
I believe the implementation of a LEFT JOIN must include more than a
cross-product. At the very least a NULL row must be added to the right table
before performing the product.
Based on my results I would guess the optimizer stops the my query before
the server ever even tries to perform the join.
> Ah okay, I as thought, I misunderstood your objection.
>
> The behaviour is how Standard SQL defines it and what is to be
> expected. You may want consider to do some reading on relational
> database theory (don't take this proposal wrong...).
>
> Aside from that, I agree, that sometimes the behaviour you expected
> would be handy.
>
> Well, let's see if I can explain it (that's is not my strength :-):
> SQL operates (at least in theory) on sets, and the first step to
> resolve the query is to build the cross product of both tables
> involved in a join (i.e. each row from one table is put together with
> each table of the other table, creating N times M rows).
>
> Then depending on the join type, the intermediate result is
> restricted. The problem you encounter is, that if the right table is
> empty, the cross product already is empty. And there is nothing left
> to do. While, when the right table is non-empty, you get the result
> you expect.
>
> The inconsistence, as you called it, is that a LEFT JOIN is _not_
> defined the way one usually tends to think about it, namely to take
> each row from the left table and simply look whether there is a match
> in the right table. It is more complex, and admittingly not as
> intuitive as it could be, but the way, RDBMs based on SQL work.
>
> Bye,
>
> Benjamin.
>
>
>
> On Wed, Mar 21, 2001 at 12:37:56AM -0700, [EMAIL PROTECTED] wrote:
> > The query returns 2 different result sets depending on whether the table
on
> > the right side of a LEFT JOIN has records or not. I just don't see how
this
> > is acceptable behavior. Either it should always return the result set or
it
> > should always return the empty set. The size of the right table should
not
> > affect the size or the result in a LEFT JOIN
> [...]
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
