Mysql Precedence Logic

Question

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 first part of the query ?

Correct me if I'm wrong

Thank you

Jacob · Accepted Answer · 2011-08-22 18:49:33Z

3

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

answered Aug 22, 2011 at 18:49

Jacob

43.6k6 gold badges81 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Derek · Accepted Answer · 2011-08-22 18:49:07Z

1

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.

answered Aug 22, 2011 at 18:49

Derek

23.5k8 gold badges55 silver badges61 bronze badges

Comments

Paul Sasik · Accepted Answer · 2011-08-22 18:50:51Z

1

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.

answered Aug 22, 2011 at 18:50

Paul Sasik

81.9k23 gold badges152 silver badges194 bronze badges

Collectives™ on Stack Overflow

Mysql Precedence Logic

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related