Put two equal conditions into one WHERE clause – SQL

I am doing the SQL exercise and got a question about WHERE clause.

 - Write an SQL query to find all numbers that appear at least three times consecutively. 

*Logs table:*

id   Num
 1    1
 2    1
 3    1
 4    2
 5    1
 6    2
 7    2

My sql query code is:

SELECT DISTINCT a.Num AS ConsecutiveNums FROM Logs AS a
INNER JOIN Logs AS b ON  a.Id + 1 = b.Id
INNER JOIN Logs AS c ON a.Id + 2 = c.Id
WHERE a.Num = b.Num = c.Num;

When I write condition as WHERE a.Num = b.Num = c.Num, the result shows {"headers": ["ConsecutiveNums"], "values": [[1], [2]]}, which is not correct. But when I separate the condition to WHERE a.Num = b.Num AND b.Num = c.Num, the result is {"headers": ["ConsecutiveNums"], "values": [[1]]}, which is correct.

Could anyone please tell the principle of the WHERE clause regarding this situation?


The correct way to do it is with:

WHERE a.Num = b.Num AND b.Num = c.Num

When you use:

WHERE a.Num = b.Num = c.Num 

the result you get is coincidental.

It has to do with the operator = and MySql’s feature to evaluate boolean expressions like x = y as 1 for true and 0 for false.

So, this boolean expression:

a.Num = b.Num = c.Num

is evaluated as:

(a.Num = b.Num) = c.Num

and since the result of (a.Num = b.Num) can be either 0 or 1, the whole expression is equivalent to:

0 = c.Num


1 = c.Num 

But there is no row with Num = 0 in the table, so the only case that the boolean expression will evaluate to 1 (true) is if Num = 1 and this is the result you get (coincidentally).