105

Assume that category_id is an index key (not primary key) of table books. Is there any difference between the following two SQL statements?

SELECT * FROM books WHERE author='Bill' AND category_id=1

SELECT * FROM books WHERE category_id=1 AND author='Bill'

I guess filtering records first by category_id and then by author is faster than filtering them in reverse order. Are SQL engines smart enough to do it this way?

powerboy
  • 10,523
  • 20
  • 63
  • 93
  • 1
    I have searched before I posted. Anybody knows how to edit this post to make it easier to be searched out when someone else has the same question later? – powerboy Jun 30 '10 at 18:34
  • @OMG _ Remus Rusanu posted this link on a question quite recently. Maybe it was that? http://rusanu.com/2009/09/13/on-sql-server-boolean-operator-short-circuit/ – Martin Smith Jul 01 '10 at 00:18
  • @Martin Smith: Didn't know Remus had a blog, very cool. Got a link for where the blog link was posted? – OMG Ponies Jul 01 '10 at 00:22
  • @OMG - Yep. It was here... http://stackoverflow.com/questions/3088709/what-is-query-execution-doing-to-this-query-in-sql-server-2005/3088835#3088835 – Martin Smith Jul 01 '10 at 00:35
  • 2
    Possible duplicate of [Does order of where clauses matter in SQL](http://stackoverflow.com/questions/11436469/does-order-of-where-clauses-matter-in-sql) – Viktor Mellgren Oct 19 '16 at 07:54

5 Answers5

102

No, the order of the WHERE clauses does not matter.

The optimizer reviews the query & determines the best means of getting the data based on indexes and such. Even if there were a covering index on the category_id and author columns - either would satisfy the criteria to use it (assuming there isn't something better).

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Although I do agree one thing I would add is that I would order your logic that will pass right away when using or statements or might fail right away when using and statements. No need to check other criteria if you can find out right away. – spinon Jun 30 '10 at 18:30
  • Can statistics that are not up to date affect this? – Abe Miessler Jun 30 '10 at 18:30
  • 1
    @Abe Miessler: Yes, statistics and indexes being out of date can negatively impact what the optimizer chooses. But the more data, the more costly it can be to keep these up to date. – OMG Ponies Jun 30 '10 at 18:33
  • Is the same thing true for MySQL? – Drew Sep 27 '11 at 05:42
  • 1
    @AndrewHeath: To my knowledge, this is applicable to databases (short of NoSQL variants) – OMG Ponies Sep 27 '11 at 14:03
  • what about something like this. Where isnumeric(MyNvarchar) = 1 and convert(numeric, MyNvarchar) > 5. my MyNvarchar is sometimes numeric. If the second condition is applied first, it will fail sometimes. – TizzyFoe Jan 18 '21 at 20:37
20

SQL is declarative.

In your example, you have told the engine/optimizer what you want... it will now work out the best way to do that (within reason and "cost" which would be off topic).

gbn
  • 422,506
  • 82
  • 585
  • 676
  • 6
    +1 for the true statement. That said, Prolog is declarative and yet (at least with implementations I've used) the order of conditions matters. – Justin K Jun 30 '10 at 19:05
9

Whereas in general, no, that assumes you're using a modern database. Maybe ten years ago, it certainly mattered then.

Dean J
  • 39,360
  • 16
  • 67
  • 93
3

In short, no, they do not matter as the optimizer will determine the best means for fetching the data.

ajdams
  • 2,276
  • 14
  • 20
1

Yes, SQL is a declarative language. But in SQL Server (not sure about other engines) a DBA can actually (kinda) do this, by forcing an execution plan in SQL Query Store.

enter image description here

But, yeah, you can't control it from your app, or from within the query text itself.

P.S. 2 more cents: you can control the order of JOIN's by using FORCE ORDER.

Alex from Jitbit
  • 53,710
  • 19
  • 160
  • 149