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