sqljoinwhere-clause

SQL JOIN where to place the WHERE condition?


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?


Solution

  • If a filter enters in a JOIN condition functionally (i.e. it is an actual join condition, not just a filter), it must appear in the ON clause of that join.

    Worth noting:

    ... table1 t1 LEFT JOIN table2 t2 ON ... AND t2.column = 5
    

    is correct

    ... table1 t1 LEFT JOIN table2 t2 ON ... 
    WHERE t2.column = 5 
    

    is incorrect, as t2.column = 5 tells the engine that records from t2 are expected, which goes against the outer join. Exception to this would be an IS NULL filter, such as WHERE t2.column IS (NOT) NULL (which is in fact a convenient way to build conditional outer joins)

    Hope it helped.