On 23. Sep 2008, at 23:26, Bill Karwin wrote:
SELECT `outer`.*, `t2`.*, `t1`.*
FROM `firstTable` AS `outer`
LEFT JOIN `someOtherTable` AS `t2` ON t2.foo = `outer`.foo
INNER JOIN `someTable` AS `t1` ON t1.id = t2.id
This works like the parenthesized join you showed, because t1.id =
t2.id is
not true where t2.id is NULL.
Sorry, but these are not at all equivalent.
Simplest example: if both someTable is empty, my query will return all
rows from the outer table and NULLs for the other two, while yours
will return nothing.
Generally, to the best of my knowledge, LEFT JOINs with INNER JOINed
right tables and RIGHT JOINs with INNER JOINed left tables can't
simply be rewritten without nested joins.
Jaka