23

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?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Read this http://www.sql-server-performance.com/tips/t_sql_where_p1.aspx. – rick schott Sep 22 '09 at 04:19
  • related to (and possibly a dupe of) http://stackoverflow.com/questions/642784/does-the-order-of-columns-in-a-where-clause-matter – Brad Cupit Mar 20 '13 at 22:14
  • 1
    Possible duplicate of [SQL - Does the order of WHERE conditions matter?](https://stackoverflow.com/questions/3152182/sql-does-the-order-of-where-conditions-matter) – Jens Schauder Feb 06 '18 at 08:44
  • Does this answer your question? [Does the order of columns in a WHERE clause matter?](https://stackoverflow.com/questions/642784/does-the-order-of-columns-in-a-where-clause-matter) – philipxy Jun 18 '20 at 02:31

6 Answers6

22

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.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
  • +1 for the details, explaining query optimization in a paragraph or two is a pretty tall order. – rick schott Sep 22 '09 at 04:31
  • Not disagreeing, just sharing an unusual example... We ran into a scenario where a third-party encryption routine used in SQL 2000 would severely impact the performance of a query if we included it as the first expression in a where clause as opposed to the last expression. When first, decryption was applied against the entire set of records in the table. When last, decryption was applied against a smaller subset of records in the table. The queries in question went from excessive (30 sec, 3 min, etc.) to acceptable (1-2 seconds). – Mayo Sep 23 '09 at 14:26
  • @Mayo: interesting example...ideally, there would be a way for 'you' (the providers of the 3rd party encryption routine) to tell the optimizer that 'this function costs an arm and a leg; invoke when necessary, but don't invoke until you know it is necessary'. If, as I expect, the optimizer simply assumed "it's a function; it costs as much as LENGTH() or some other function", then it could make misguided judgements about the sequence. What you highlight is that if a cost-based optimizer does not have a good idea of the cost of something, it will sometimes make lousy decisions. Thanks for this! – Jonathan Leffler Sep 23 '09 at 15:02
13

No, it doesn't. Most modern SQL servers include a query optimizer which looks into all the plausible (*) ways of resolving a query and whereby older servers may take hints based on the order within the SELECT clause, newer servers do not.

The order of the JOINs on the other hand still matter to a greater extent.

Edit: Do see Jonathan's Leffler's response for he provides additional detail in particular regarding the order of JOINs. Thanks you, Jonathan!

Edit: ( * ) Plausible vs. Possible: As pointed out by Erikkalen, the optimizer does not look into all of the possible ways, thanks to [pretty good] heuristics coded in its logic, it will only evaluate the plausible plans, on the basis of the statistics it keeps for the underlying indexes. For each of the plans it considers an overall cost is estimated (or partially so, when partial costs readily exceed the overall cost of another plan [pruning]), and that's how the plan effectively used is eventually selected. While the general principles used by SQL query optimizers are well known, the intricacies of their implementation introduce many different twists-and-turns.

mjv
  • 73,152
  • 14
  • 113
  • 156
  • 2
    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 Table2 LEFT JOIN Table1. But for INNER JOIN, the sequencing should not matter. The processing order may be forced, to some extent, if you are dealing with a star join, for example. – Jonathan Leffler Sep 22 '09 at 04:20
  • @jonathan, can you say that again ... you lost me a little (but it sounds very interesting) –  Sep 22 '09 at 04:23
  • See the added material to my answer :D – Jonathan Leffler Sep 22 '09 at 04:24
  • No, it will not look in to "all of the possible ways" of resolving a query since that would be O(n!). It will take into account some of them and hopefully the best ones. You might, by sheer luck, get a faster execution plan by reordering items in the where clause. – erikkallen Sep 23 '09 at 09:00
  • @erikkalen. Agreed on the _all possible ways_, see my edit. Now, as to moving things in the input query in order try one's "sheer luck" at outsmarting the optimizer, I can't dismiss it, but the odds of such a thing are rather low... A better way to respectfully [;-)] help the optimizer, if the SQL implementation allows it, is by way of the explicit HINT construct. – mjv Sep 23 '09 at 14:27
  • if you get the same results from doing any type of join between table 1 and table 2, would it make a difference if you are doing left outer vs inner vs right outer except cross join ofc – PirateApp Mar 12 '18 at 11:52
  • 1
    @PirateApp ATTENTION! Different types of join can yield different results altogether (more / fewer rows and different columns with NULLs etc.). The original question is about _performance_. Now, if, as you change the order of the tables, you choose an alternate join specifier as so to get the same logical query (hence the same results), the performance should typically be similar. Yet -again- the order and type of JOINs tend to influence the behavior of the query optimizer more readily, and hence performance consistency may vary between SQL implementations and/or different datasets., – mjv Mar 17 '18 at 04:11
  • @mjv thanks for the headsup, what i meant is lets say i have a table A and table B such that all rows in table A are found in B more than once, inner, left and right join return the same results, in this scenario, does it matter what type of join I do? – PirateApp Mar 17 '18 at 06:37
  • 1
    @PirateApp If A and B have the same set of join keys (i.e. "all rows in A are found in B" as you wrote _and_ all rows in B are found in A), then yes, the inner, left and right joins are logically same and _on most modern SQL implementations_ the particular join type used should not affect performance in a significant fashion. My response is qualified "yes": typically / in-general... the type of join should not affect performance. Devil is in the details: DBMS used and underlying data context (e.g. Index stats up to date? Which indexes are available? Relative size of tables? ...) – mjv Mar 19 '18 at 14:24
8

See below and follow the link(long article but worth the read):

SQL Server Transact-SQL WHERE

If a WHERE clause includes multiple expressions, there is generally no performance benefit gained by ordering the various expressions in any particular order. This is because the SQL Server Query Optimizer does this for you, saving you the effort. There are a few exceptions to this, which are discussed on this web site. [7.0, 2000, 2005] Added 1-24-2006

rick schott
  • 21,012
  • 5
  • 52
  • 81
3

It depends on the DBMS. SQL itself does not say anything about how a query should execute. It is up to the specific implementation.

If your DBMS had the very simplistic model of interpreting the query sequentially, then putting a > 1 first in your example would (obviously) be faster - because the DBMS would make two passes of which the second pass is through a much smaller resultset.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
Dawie Strauss
  • 3,706
  • 3
  • 23
  • 26
2

No. The optimiser decides which order to filter results based upon current statistics.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • Is that true even if I know one clause will drastically reduce/filter the resultset more than another clause. Meaning, what happens if I know "a > 1" will drastically reduce the resultset moreso than "b < 2". Does the order then matter? –  Sep 22 '09 at 04:21
  • No - if you know it, the optimizer should too, and will probably use the more selective condition first. It depends on whether there are indexes to help, and whether a and b are in the same table, and ... – Jonathan Leffler Sep 22 '09 at 04:32
0

If it's from the same table, and the query is as simple as your example then, no it doesn't make a difference. As you get more complicated and link more tables, it can.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Zenshai
  • 10,307
  • 2
  • 19
  • 18
  • So what, in order, should come first? The filter than narrows down the result set the most - first ... or last in the order of the WHERE clause? –  Sep 22 '09 at 04:14