CODEWITHSUNDEEP
mysqlwhere-clause
Dil.
Dil.

Reputation: 2076

what is the where clause hierarchy in mysql

I faced this question in an interview. They asked is there any hierarchy.
Ex: SELECT * FROM invoice WHERE invoiceID=100 AND grossAmount>2000 AND customerName= 'Adam'
Is there a special hierarchy to add those 3 conditions? Something Like check numeric condition first? Please give me your opinion.

Upvotes: 0

Views: 98

Answers (2)

Polynomial Proton
Polynomial Proton

Reputation: 5135

Here is the official documentation on Oracle's website

In your case, the query will run as its written since = and < have same operator precedence.

SELECT * FROM invoice WHERE (invoiceID=100 AND grossAmount>2000 AND customerName= 'Adam')

If it was an OR clause 

 SELECT * FROM invoice WHERE (invoiceID=100) OR (grossAmount>2000 AND customerName= 'Adam')

Then the AND would run first and then OR. Only in cases where same operators exists then it gets to = + etc. Check documentation for order.

Upvotes: 1

n8wrl
n8wrl

Reputation: 19765

The query optimizer will look at the conditions in the WHERE clause and evaluate them in whatever order it finds that:

  1. Ensures correctness
  2. Takes advantage of indexes and other information about the DB

For example, if you had an index on invoiceID it might evaluate that first so that it had fewer rows to examine in checking customerName and grossAmount.

Your example is all 'AND' clauses so there is no precedence involved.

Upvotes: 2

Related Questions