Re: LEFT JOIN problem

Mael Rimbault <mael.rimbault@gmail.com> writes:
> 2013/8/10 Michael Rowan <michael.rowan3@gmail.com>:
>> SELECT co_id, de_id, or_id
>> FROM orders, depots, companies
>> LEFT JOIN addresses ON (de_ad_id=ad_id)
>> WHERE or_id=de_or_id AND co_id=de_co_id;
>>
>> returns error
>> ERROR:  invalid reference to FROM-clause entry for table "depots"
>> LINE 1: ...pots, orders, companies LEFT JOIN addresses ON (depots.de_...
>>                                                            ^
>> HINT:  There is an entry for table "depots", but it

> The "invalid reference" error means that, from the "LEFT JOIN" part of
> the query, you cannot refer to columns belonging to the "depots"
> table.
> This may be due to the fact you're mixing implicit and explicit joins
> in your query.

For some context here: the SQL standard specifies that JOIN binds tighter
than comma in a FROM list.  So the implied parenthesization of what you
wrote is (abbreviating the table names for convenience)

      FROM o, d, (c LEFT JOIN a ON (...))

and the ON-condition is only allowed to reference tables c and a.

You might have gotten this code, or at least this habit, from old versions
of MySQL, which got the SQL standard wrong and considered that comma and
JOIN bind equally strongly left-to-right, thus allowing an ON condition
placed like this to refer to o and/or d as well.  I'm fairly sure they
fixed that awhile ago, though.

If you really want to write in this style I'd suggest replacing the commas
by CROSS JOINs:

      FROM o CROSS JOIN d CROSS JOIN c LEFT JOIN a ON (...)

Now the JOINs all bind equally strongly left to right, so the effect is

      FROM ((o CROSS JOIN d) CROSS JOIN c) LEFT JOIN a ON (...)

and the ON condition can refer to any of the tables.

But, as Mael suggested, it might be better to think a bit harder about
exactly what you're expecting the LEFT JOIN to do, and swap the placement
around accordingly.

            regards, tom lane