asked
How is the performance affected by placing filters in where clause or in join
We had some debate few days ago regarding placement of filtering conditions in our queries. A lot of our tables have `status` column which can be `(I)nactive` or `(A)ctive`. We are used to specify the queries as follows: SELECT t1.column1, t2.column1, t2.column3 FROM table1 t1 INNER JOIN table2 t2 ON (
t1.id = t2.t1_fk AND t1.status='A' AND t2.status='A') But we are skeptical if putting the status check on separate `Where` clause will provide any benifits: SELECT t1.column1, t2.column1, t2.column3 FROM table1 t1 INNER JOIN table2 t2 ON (
t1.id = t2.t1_fk) WHERE t1.status='A' AND t2.status='A' We tried some testing and results were inconsistent. For some tables it speed up fetching rows with separate `Where` clause but for others it degraded performance considerably.
In theory, it shouldn't matter where you place that criteria, as long as we're talking about inner joins. But in practice, as you've already seen, it can affect performance. As a general rule, I recommend that you put JOIN criteria in the ON statement and filtering criteria in the WHERE clause and that you don't mix the two. You need to look at the execution plans. If you're getting a timeout on the plan, then the optimizer didn't have enough opportunity to rearrange the criteria to find an optimal solution.
In following Grant's advice you also gain in clearer code. Month's later you or someoone new to it will be able to quickly see which are the JOIN compnents and which are the filters.
16 People are following this question.
