On Wed, Mar 21, 2001 at 01:32:11AM -0700, [EMAIL PROTECTED] wrote:
> 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.
It would. Let's see an minor example:
table1:
id
1
2
3
table2:
id
1
3
cross product:
table1.id table2.id
1 1
1 3
2 1
2 3
3 1
3 3
SELECT table1.id, table2.id FROM table1 LEFT JOIN table ON table1.id = table2.id-1
(note the '-1' which is there to get away from the trivial case)
will result in:
1 NULL
2 3
3 NULL
because, if you simply go through all all table1.id's, you will find
the '3', but not a matching '3' (4-1) and therefore get a NULL. It's
as if you group on table1.id and look if there is a match in the
group:
grouped cross product:
table1.id table2.id table2.id-1
1 (1,3) (0,2)
2 (1,3) (0,2)
3 (1,3) (0,2)
If there is a match in the group, the matching (table2.id-1) is taken,
else NULL is printed. So I can very well derive the NULL values only
from the cross product.
> 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.
Sorry, I don't understand you here. Maybe we don't think the same
about what cross product is (see above).
> Based on my results I would guess the optimizer stops the my query
> before the server ever even tries to perform the join.
That's a different thing. What I explained was how the theory goes.
The optimizer is free to do what it wants as long it complies to the
theory. In your case, it early recognizes that the right table is
empty and knows, that the result will be empty, regardless of what
kind of LEFT JOIN this is and therefore stops. (At least, that is what
I assume that happens... I didn't look at the source).
Bye,
Benjamin.
---------------------------------------------------------------------
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
