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. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
