SQL JOIN where to place the WHERE condition?

Question

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

2. Example (JOIN AND)

SELECT 1

  FROM table1 t1

  JOIN table2 t2 ON t1.id = t2.id AND t2.field = true

What is the faster way in terms of performance? What do you prefer?

Answer 1

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.

SQL JOIN where to place the WHERE condition?

Sign up