1

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

Zhouster
  • 746
  • 3
  • 13
  • 23
  • Use `Inner Join` instead – Hotdin Gurning Aug 18 '15 at 03:43
  • Hmmm, why does that fix things? – Zhouster Aug 18 '15 at 03:44
  • I've never seen that before. Are you sure it's not just evaluating one of those equivalences and applying it as an Intoolean to the next? (Ok, yes, I did just coin a term. I mean a Boolean, which is subsequently reinterpreted as an integer with value 0 or 1.) Or is that actually valid syntax in MySQL? – lc. Aug 18 '15 at 03:45

2 Answers2

4

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.

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
0

I think the difference is that in the first WHERE all three ids must be equal, whereas in the latter a.id should be equal to b.id and b.id should be equal to c.id but it doesn't mean that a.id should be equal to c.id.

SergeyAn
  • 754
  • 6
  • 9