 June_Stephanie - 4 years ago 90
MySQL Question

# Why MYSQL returns different result for a+2=b+1=c vs a+1 = b and b+1=c?

I write a SQL query to find all numbers that appear at least three times consecutively:

| Id | Num|

| 1 | 1 |

| 2 | 1 |

| 3 | 1 |

| 4 | 2 |

| 5 | 1 |

| 6 | 2 |

| 7 | 2 |

For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.

My original query returns 1 and 2:

`````` SELECT l1.Num
FROM Logs l1, Logs l2, Logs l3
WHERE l1.Id + 2 = l2.Id + 1 = l3.Id
AND l1.Num = l2.Num = l3.Num;
``````

+------------------------------------------------------------------------

Then I change my query to this (basically I change the a+2 = b+1 = c to a + 1 = b and b+1 = c), it returns the correct answer:

``````SELECT l1.Num
FROM Logs l1, Logs l2, Logs l3
WHERE l1.Id + 1 = l2.Id AND l2.Id + 1 = l3.Id
AND l1.Num = l2.Num AND l1.Num= l3.Num;
``````

+------------------------------------------------------------------------

It drives me crazy, I cannot figure our why. Could anyone kindly explain this to me? Thanks in advance!!!! Chirs
Answer Source

Well, let's have a look at this query: ``` WHERE l1.Id + 2 = l2.Id + 1 = l3.Id ``` In MySQL as in most programming languages, the `=` is just a binary operator, which means it takes a left and a right side and returns true if they are the same and false if otherwise.

In your query, you're first comparing `l1.Id + 1` with `l2.Id` resulting in a truth value. And then, you compare the truth value with `l3.Id`. (The truth value gets automatically casted to `0`/`1`). This leads to a different result.

tl;dr: `=` in MySQL is not like `=` often used in mathematical notation.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download