Nicholas Flees Nicholas Flees - 1 year ago 148
SQL Question

MySQL Chained Inequality

MySQL doesn't complain when "chained inequality" syntax is used in a query's

clause, but produces a result that is different from a query of a similar spirit, written with proper syntax. So what query is it actually running and what constraints are being applied? And why doesn't it produce an error?

A simplistic example: Suppose I have a table in a MySQL database a table called User with an attribute called

I can write a query of the following form with chained inequality syntax:

WHERE 10 < score < 15;

and a query of the following form with proper syntax:

WHERE score > 10 AND score < 15;

with different results.

To repeat the questions:

  1. What query is actually being run with the chained inequality? (Specifically, what are the constraints applied?)

  2. Why doesn't MySQL produce an error?

Answer Source

According to the documentation:

For operators that occur at the same precedence level within an expression, evaluation proceeds left to right, with the exception that assignments evaluate right to left.

MySQL will evaluate this as:

WHERE (10 < score) < 15

Because MySQL treats boolean values as numbers in a numeric context, the parenthesized part is turned into 0 or 1 (or NULL if score is NULL) for the subsequent comparison. So, the result is always true.

I believe this explains both why MySQL doesn't produce an error and why you are getting different results from what you expect.

You have various options using AND, but this would be typical:

WHERE score > 10 AND score < 15
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download