Re: Outer join with where conditions

On Mon, 14 Nov 2005, [ISO-8859-2] Micha� Otroszczenko wrote:

> I wonder If I could move additional join condition from ON part of
> query to where part.
>
> For example instead of:
>
> SELECT *  FROM
>     booking_load AS bload
>     LEFT OUTER JOIN dict_load_type_tsl AS load_tsl ON (
>                     load_tsl.dict_load_type_id = bload.dict_load_type_id
>                     AND load_tsl.dict_language_id = 'EN' ))
>
> Could I write:
>
> SELECT *  FROM
>     booking_load AS bload
>     LEFT OUTER JOIN dict_load_type_tsl AS load_tsl USING (dict_load_type_id)
> WHERE
>      load_tsl.dict_language_id = 'EN'
>
> I thought that second query could be more 'optimizable', but gave no results.
> Where is the problem ?

This is probably due to the differences in semantics between the two
queries as we interpret them.

We treat a condition in ON as part of the join itself, therefore the first
query is basically join rows of booking_load with rows of
dict_load_type_tsl that have a dict_language_id equal to 'EN' and the same
dict_load_type_id and if no such rows in dict_load_type_tsl are found
extend with NULLs.

Conditions in WHERE are conditions logically applied after the join, so
the second query is join rows of booking_load with rows of
dict_load_type_tsl that have the same dict_load_type_id and if no such
rows in dict_load_type_tsl are found extend with NULLs then throw out any
rows for which dict_language_id is not equal to 'EN'.

If for example, there wasn't a matching dict_load_type_tsl row, in the
first, you'd get a NULL extended row, but in the second, the row generated
by the join (NULL extended) would fail the WHERE condition and not be
returned.