Mysql Precedence Logic

Any explanation to the following queries :

Select x FROM y WHERE a = 1 OR a = 2  AND (b = 1 OR b = 2)

why it doesn't return the correct info while this return the correct info :

Select x FROM y WHERE (a = 1 OR a = 2) AND (b = 1 OR b = 2)

Am i missing something here ?

      X   Y   (X OR Y)  X OR Y   


      1   0     1         1 
      0   1     1         1
      1   1     1         1
      0   0     0         0 

I know in term of precedence the () have priority , but why should i add them the the fir开发者_开发知识库st part of the query ?

Correct me if I'm wrong

Thank you

AND has a higher precedence than OR so your first query is equivalent to this:

Select x FROM y WHERE a = 1 OR a = 3 OR (a = 2  AND (b = 1 OR b = 2))

Which is not equivalent to

Select x FROM y WHERE (a = 1 OR a = 2 OR a = 3) AND (b = 1 OR b = 2)

I guess you forgot the a = 3 part in your first query.

Operator precedence in MySQL

Because ambiguity is an undesirable trait?

Also, the optimizer will re-order your WHERE Conditions if it thinks it will perform better. Your ambiguity will, therefore, cause different results depending on how/what it evaluates first.

Always be explicit with your intentions.

Using parentheses in your WHERE clause does not just affect precedence but also groups predicates together. In your example the difference in results is more a matter of grouping rather than precedence.

You could think of this: (pN = predicate expression)

WHERE a = 1 OR a = 2  AND (b = 1 OR b = 2)

as:

WHERE p1 OR p2 AND p3

And this:

WHERE (a = 1 OR a = 2 OR a = 3) AND (b = 1 OR b = 2)

as:

WHERE p1 AND p2

and so it becomes clear that the results could be quite different.