CODEWITHSUNDEEP
mysqlsql
Navin
Navin

Reputation: 31

Order of execution in where condition

Select * from tableA
Where gender=‘m’ and (job title = ‘manager’ or marital_status=‘m’)

Which condition will be executed first in the where clause?

I.e:

Upvotes: 0

Views: 183

Answers (1)

The Impaler
The Impaler

Reputation: 48769

In general the order of evaluation of an expression is not established in SQL.

Database engines are free to evaluate in any order, since they are free to optimize the query. For example:

  • If the table has an index on job_title, the engine may FIRST use job_title as "access", and THEN use marital_status and gender as "filtering".

  • Alternatively, if the table has an index on gender, the engine may FIRST use gender as "access" and THEN use marital_status and job_title as "filtering".

Do you clearly see the difference between access and filter? All engines need an "access predicate" to retrieve the data, and then they use "filter predicates" to choose which rows are to be returned.

Some times we would like to know the order of the expression evaluation, specifically to short-circuit boolean conditions. This does not work in SQL -- a declarative language -- the way it does in imperative languages (Java, PHP, C#, etc.)

Upvotes: 2

Related Questions