Search code examples
mysqlwhere-clauseequivalence

MySQL - Multiple equivalencies in WHERE clause

I was wondering if there is a difference between the following two WHERE clauses in MySQL:

WHERE a.Id = b.Id = c.Id

vs.

WHERE a.Id = b.Id and b.Id = c.Id

I am getting different results for these two queries in the following SQLFiddle: http://sqlfiddle.com/#!2/725396/3

Solution

  • As per the MySQL doco for an expression, your expression a = b = c uses the following form of boolean_primary:

    comparison_operator: = | >= | > | <= | < | <> | !=
boolean_primary:
    boolean_primary comparison_operator predicate

    and is therefore equivalent to:

    (a = b) = c

    This means you'll get a truth value from a = b and then compare that with c.

    In MySQL, the boolean type is equivalent to tinyint(1), where 0 is false and anything else is true. Hence it's simply an integral value which can be compared with another.

    Further, the result of a comparison expression like a = b will always return 0 for false, 1 for true, or NULL if any input is NULL.

    Therefore, for the expression a = b = c to be true, none of them are allowed to be NULL, and one of the following must be true:

    • a is equal to b, and c is equal to 1; or
    • a is not equal to b, and c is equal to 0.

    That's almost certainly not what you want, the correct form is the second one.