0
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:

  • First it checks gender and if it satisfies then it checks for (job title = ‘manager’ or marital_status=‘m’)
  • Or it executes (job title = ‘manager’ or marital_status=‘m’) and then check for gender.
The Impaler
  • 45,731
  • 9
  • 39
  • 76
Navin
  • 31
  • 1
  • Possible duplicate of [MySQL query / clause execution order](https://stackoverflow.com/questions/24127932/mysql-query-clause-execution-order) – mypetlion Sep 14 '18 at 21:39
  • Could you provide some sample data and expect result? – D-Shih Sep 14 '18 at 21:53
  • Possible duplicate of [Order of ANDS in Where clause for greatest performance](https://stackoverflow.com/questions/3805863/order-of-ands-in-where-clause-for-greatest-performance) – Powerlord Sep 15 '18 at 00:21

1 Answers1

2

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

The Impaler
  • 45,731
  • 9
  • 39
  • 76