Thread: Outer join with where conditions
Hello,
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 ?
Best regards,
Michal
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.
On Mon, Nov 14, 2005 at 14:45:22 +0100, Michał Otroszczenko <michal.otroszczenko@gmail.com> wrote: > Hello, > > I wonder If I could move additional join condition from ON part of > query to where part. Yes, but the semantics are different for outer joins. > > 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 ? > > Best regards, > Michal > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend
> 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'. > Thank you for extensive explanation. I supposed that it is like that, but I couldn't find confirmation in docs. Regards, Michal