Re: [GENERAL] Outer join with where conditions

[Michał Otroszczenko]

> 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

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[GENERAL] Outer join with where conditions

[Michał Otroszczenko]

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

---(end of broadcast)---
TIP 6: explain analyze is your friend