Tutorial :SQL - Does the order of WHERE conditions matter?

Question:

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?

Solution:1

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

Solution:2

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

Solution:3

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

Solution:4

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

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com