performance - SQL question: Does the order of the WHERE clause make a difference?

Question

From a performance standpoint, does the order of my SQL WHERE statements make a difference?

For instance

SELECT ... FROM ...
WHERE a > 1
AND b < 2

Would that be any faster/slower than

SELECT ... FROM ...
WHERE b < 2
AND a > 1

Let's also assume that I know in advance that a > 1 will narrow the result set the most.

Also, does it matter if I'm joining two or more tables the order of my WHERE statements?

Answer 1

In theory, there is no difference.

Occasionally, especially with the simpler optimizers, there are differences in the query plan depending on the order of the clauses in the WHERE clause. There's a moderately strong argument that such differences are symptomatic of a bug.

Similar comments apply to join order, too. The order of the joins should not matter - for joins of the same type. Clearly, whether a table Table2 is inner joined or outer joined to another table Table1 does matter - and it matters whether it is Table1 LEFT JOIN Table2 or Table1 RIGHT JOIN Table2 or Table1 FULL JOIN Table2. But for a series of INNER JOIN operations, the sequencing should not matter. The processing order may be forced, to some extent, if you are dealing with a chain of joins.

Clarifying (again) - consider:

(Table1 AS t1 JOIN Table2 AS t2 ON t1.pkcol = t2.fkcol) AS j1
JOIN
(Table3 AS t3 JOIN Table4 AS t4 ON t3.pkcol = t4.fkcol) AS j2
ON j1.somecol = j2.anothercol

The way it is written, clearly the programmer expects the joins on (t1, t2) and (t3, t4) to be executed before the join on (j1, j2), but the optimizer may be able to do the joins differently. For example, if j1.somecol derives from Table1 and j2.anothercol derives from Table4, the optimizer may be able to choose the join on Table1.SomeCol = Table4.AnotherCol over either of the other joins. This sort of issue can be influenced by the filter conditions in the WHERE clause, and by the presence or absence of appropriate indexes on the various tables. This is where statistics can play a big part in the way the optimizer generates the query plan.