It seems it is not possible to combine the MySQL IN command with a field name, even when that field contains comma delimited values. MySQL may match the first value, but not any of those following the comma.
SELECT name FROM pet_owners WHERE 'FIDO' IN (pet_owners.pets)
The above code doesn’t work, I suspect because
... IN (pet_owners.pets) ...
is treated not as
... IN ('FLUFFY' , 'FIDO' , 'SPOT') ...
... IN ('FLUFFY,FIDO,SPOT') ...
One method is to use Regular Expresisons:
SELECT name FROM pet_owners WHERE pet_owners.pets REGEXP '[[:<:]]FIDO[[:>:]]'
Another way would be to use the FIND IN SET command:
SELECT name FROM pet_owners WHERE FIND IN SET ('FIDO', pet_owners.pets) > 0
The pet_owners.pets field does not have to be set to datatype SET as long as it has comman separated values.