CODEWITHSUNDEEP
sql
powerboy
powerboy

Reputation: 10961

SQL - Does the order of WHERE conditions matter?

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?

Upvotes: 110

Views: 44406

Answers (5)

Alex from Jitbit
Alex from Jitbit

Reputation: 60546

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.

Upvotes: 1

Dean J
Dean J

Reputation: 40288

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

Upvotes: 10

gbn
gbn

Reputation: 432180

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).

Upvotes: 22

OMG Ponies
OMG Ponies

Reputation: 332491

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).

Upvotes: 110

ajdams
ajdams

Reputation: 2314

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

Upvotes: 4

Related Questions