Adzz Adzz - 5 months ago 8
SQL Question

Why does comparing types in MYSQL not raise an error?

This:

SELECT * FROM table WHERE comlumn1 = column2;


returns results.

This:

SELECT * FROM table WHERE column1 != column2;


returned nothing, even though there are cells in the columns that satisfy both requests.

It turned out that column1 was a
varchar
and column2 was an
int
.

My question is why did it not error? Why would one return results and not the other? If the types are different, surely they are always not equal?

UPDATE:

The values in
column1
were
varchars
like
1234-34
and
column2
had
ints
like
1234
. This meant it would always match because it would turn the
varchar
1234-34
into
1234
.

Answer

When comparing a string with a number, MySQL tries to convert the string to a number to make the comparison.

If not possible the number result of the string is 0.

Examples:

string value  |   int value  |   comparison result
'abc'         |   1          |   0 = 1 -> false
'1'           |   1          |   1 = 1 -> true
'1abc'        |   1          |   1 = 1 -> true
'abc1'        |   1          |   0 = 1 -> false