Search code examples
mysqlwhere-clausein-clause

MySQL Where IN Clause returns different result

I am new in MySQL, I would like to understand how IN clause works.

In MySQL when I try to use in IN clause as such:

Select *
From `table`
WHERE `code` IN (1,2)

It works and it returns the record

Select *
From `table`
WHERE `code` IN (3)

It does not work.

Column value in DB:

-----------------
|    `code`     |
|    1,3,2      |
-----------------

desc: code VARCHAR(100)

Solution

  • When string is compared with a number, the string is converted to a number. So this:

    SELECT '1,3,2' IN (1, 2) -- true

    Would force MySQL to convert 1,3,2 to 1; it parses 1 and ignores everything after comma. You can confirm this behavior by re-arranging the values and it will no longer match:

    SELECT '3,1,2' IN (1, 2) -- false

    A hackish solution is to do something like this:

    WHERE CONCAT(',', code, ',') LIKE '%,1,%'
   OR CONCAT(',', code, ',') LIKE '%,2,%'

    But the correct solution is to store comma separated values as separate rows.