48

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?

Aley
  • 8,540
  • 7
  • 43
  • 56

3 Answers3

24

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:

  • If you place it in the WHERE clause instead, the performances are the same if the join is INNER, otherwise it differs. As mentioned in the comments it does not really matter since anyway the outcome is different.

  • Placing the filter in the WHERE clause when it really is an OUTER JOIN condition implicitely cancels the OUTER nature of the condition ("join even when there are no records") as these filters imply there must be existing records in the first place. Example:

... table1 t LEFT JOIN table2 u ON ... AND t2.column = 5 is correct

... table1 t LEFT JOIN table2 u 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)

  • LEFT and RIGHT joins are implicitely OUTER joins.

Hope it helped.

Sebas
  • 21,192
  • 9
  • 55
  • 109
  • 2
    This answer contains some wrong and muddled writing. 1. For INNER JOIN any condition can be in a WHERE instead of an ON as long as there is no intervening OUTER JOIN. 2. When moving a LEFT JOIN condition from an ON to a WHERE the performance is irrelevant since (as you say) in general the result differs. 3. That difference does not in general "transform the OUTER JOIN into an INNER JOIN". – philipxy Jun 27 '16 at 12:16
  • What do either of 'a filter enters in a JOIN condition functionally' or '[a filter] is an actual join condition, not just a filter' mean? Anyway the notion of 'just a filter' is not helpful since the meaning of `JOIN ON c AND d` is the same as `JOIN ON c WHERE d`. (In fact the SQL standard *defines* JOIN ON in terms of JOIN WHERE.) Similarly "implicitely cancels the OUTER nature of the condition" *just doesn't communicate anything*. Which you seem to agree with since you annotate it with the scare-quoted (hence itself unclear) '("join even when there are no records")'. You're not explaining. – philipxy May 01 '17 at 11:01
  • PS 'these filters imply there must be existing records in the first place' is unclear and when taken at face value is not correct, and 't2.column = 5 tells the engine that records from t2 are expected, which goes against the outer join' is unintelligible. You need to not use any of these vague everyday terms (that are presumably consistent with what you are thinking but do not explain what you are thinking) and just make statements about the parts of the query & their meanings using the technical terms that apply to them. – philipxy May 01 '17 at 11:08
  • A join condition differs from a filter in that in related tables together. A filter only applies to one table, such as in the example I wrote (table t2.column = 5). For the rest, I beg to differ. – Sebas May 01 '17 at 11:18
  • That comment doesn't parse. (But if you edit it so it does then I expect you will still be using everyday words in vague & unclear ways as already addressed. Eg a 'filter' always only applies to one table, which in the case of an ON is a cross product and in the case of a WHERE is the result of a(n INNER or OUTER) JOIN.) (So I expect to end my comments here.) – philipxy May 01 '17 at 11:53
7

JOIN conditions should normally be independent from filter conditions. You define rules of your join (the how) with ON. You filter what you want with WHERE. Performance wise, there's no general rule across all engines and designs, so your mileage will vary greatly.

MPelletier
  • 16,256
  • 15
  • 86
  • 137
0

I think the faster way is to put the filter in the where clause, because it will procees that filter in the where first , and then the join clause, so there will be no need of permutation of filters.

Artemination
  • 703
  • 2
  • 10
  • 30
  • 1
    There is no difference for a DBMS with any optimization at all. Eg see the MySQL documentation on SELECT/JOIN optimization: an INNER JOIN's ON is treated as if it were a WHERE applied to that JOIN. – philipxy Jun 27 '16 at 12:20