Why is there only column name but no where equal to value in MySQL? It is not a syntax error

Question

Why is this query not a syntax error? I have a SQL Server background and I was really surprised.

Select *

from my_table WHERE id

I thought it would validate that it has a value, but the behavior is inconsistent, when using id it returns the location with the id , but when using name it doesn't return anything:

https://www.db-fiddle.com/f/enWGyAW4BtLC64PVzkbTVK/0

Answer 1

MySQL has some behavior that does not conform to standard ANSI SQL. In this case, MySQL treats an integer zero value as false and any integer non-zero value as true. In standard SQL, integers are not the same as booleans, but in MySQL they are.

When you run the query WHERE id, it returns rows with id <> 0.

When you run the query WHERE name, it evaluates the string as an integer, which means taking the numeric value of the leading numeric character (if any) and ignoring any subsequent non-numeric characters. If there are no leading digits, the string has an integer value of 0.

When you run the query WHERE name, it will only return rows if the string stored in that column has a non-zero leading number. In your example 'outro' it only has non-digits, so the value is zero and the condition cannot be satisfied.

MySQL behaves as designed, but this is not standard SQL.