I have two following examples.
1. Example (WHERE)
SELECT 1
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
WHERE t2.field = true
SELECT 1
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id AND t2.field = true
If a filter enters in a JOIN
condition functionnaly, it must appear in the ON
clause of that join.
If you place it in the WHERE
clause instead, the performances are the same if the join is INNER join
, otherwise it differs.
Placing the filter in the WHERE
clause when in reality it belongs to an OUTER JOIN
would "transform" (in terms of outcome) the OUTER JOIN
into an INNER JOIN
, because the WHERE
conditions must be realised (mandatory). The results are obviously different, as well as the explain plans.
LEFT
and RIGHT
joins are implicitely OUTER
joins.
Hope it helped.