SQL IN query behavior

user_1124047 0
Viewed 53

Executing the following SQL statement;

select '2312' in ('2312,254,2111') as result1, 2312 in ('2312,254,2111') as result2

I am getting the following result

+---------+---------+
| result1 | result2 |
+---------+---------+
|       0 |       1 |
+---------+---------+

I would expect the opposite result. Having result1 to be true and result2 to be false. Could someone explain why?

mysql
1 Answers

Using IN() with a CSV-string as parameter is not supported.
It should be IN ('2312','254','2111') instead of IN ('2312,254,2111')

The reason for the observed behaviour is an implicit type conversion happening. Look:

SELECT 2312 IN ('2312,254,2111') -- result: 1
SELECT 2312 IN ('254,2312,2111') -- result: 0 -- interesting
SELECT 2312 = '2312,254,2111'    -- result: 1 << see ??
SELECT 2312 = '254,2312,2111'    -- result: 0

Only the first number in the string is relevant. The rest is ignored due to the implicit type conversion.

Also,

SELECT '2312' in ('2312,254,2111') -- result: 0

is false, because no type conversion happens here. '2312' does not equal the only string '2312,254,2111' in the value list and hence the IN() operator return false.

If you use a list of values for IN() instead of a CSV-string, everything works als expected:

SELECT
   2312  IN ('2312','254','2111')  -- result: 1
, '2312' IN ('2312','254','2111')  -- result: 1
,  254   IN ('2312','254','2111')  -- result: 1
, '254'  IN ('2312','254','2111')  -- result: 1
,  2312  IN (2312,254,2111)        -- result: 1
, '2312' IN (2312,254,2111)        -- result: 1
,  254   IN (2312,254,2111)        -- result: 1
, '254'  IN (2312,254,2111)        -- result: 1

From the manual:

Implicit type conversion may produce nonintuitive results:

mysql> SELECT 'a' IN (0), 0 IN ('b');
        -> 1, 1

In both cases, the comparison values are converted to floating-point values, yielding 0.0 in each case, and a comparison result of 1 (true).

user_439590
Related