How to fetch all rows which matches atleast one value from array?

Question

I have values in array as below.

$findList = array(1,2,3,6);

And I have one table as below in database.

id  sets
1    1                      
2    1,2
3    1,2,4
4    1,2,6
5    3,8,9
6    7,8,9
7    5,8,9

Now, I would like to find all rows which has any of value of array findList.

So result set should have first five rows.

I can do this by explode array and write OR query with each element. Is there any other way?

Answer 1

Leaning on Lad2025's sqlfiddle and this Clever Answer from Pavel

Schema

CREATE TABLE tab(
   id   INTEGER  NOT NULL PRIMARY KEY 
  ,sets VARCHAR(6) NOT NULL
);
INSERT INTO tab(id,sets) VALUES (1,'1');
INSERT INTO tab(id,sets) VALUES (2,'1,2');
INSERT INTO tab(id,sets) VALUES (3,'1,2,4');
INSERT INTO tab(id,sets) VALUES (4,'1,2,6');
INSERT INTO tab(id,sets) VALUES (5,'3,8,9');
INSERT INTO tab(id,sets) VALUES (6,'7,8,9');
INSERT INTO tab(id,sets) VALUES (7,'5,8,9');

Query

-- 1,2,3,6 ... seek this

select * from tab 
WHERE CONCAT(",", `sets`, ",") REGEXP ",(1|2|3|6),";
+----+-------+
| id | sets  |
+----+-------+
|  1 | 1     |
|  2 | 1,2   |
|  3 | 1,2,4 |
|  4 | 1,2,6 |
|  5 | 3,8,9 |
+----+-------+

Better yet, see comments above under question for data normalization. You might pick up optimal use of indexes if you do.

Answer 2

Is there any other way?

Just for fun you can utilize IN clause as in first comment. This code shouldn't be used on production environment :)

SELECT *
FROM tab
WHERE id IN (SELECT id
              FROM (
                SELECT id, SUBSTRING_INDEX(SUBSTRING_INDEX(t.sets, ',', n.n), ',', -1) AS val
                FROM tab t 
                CROSS JOIN 
               (
                 SELECT a.N + b.N * 10 + 1 n
                   FROM 
                  (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
                 ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
                ) n
                 WHERE n.n <= 1 + (LENGTH(t.sets) - LENGTH(REPLACE(t.sets, ',', '')))
                ) sub
              WHERE val IN (1,2,3,6) 
)

SqlFiddleDemo

Output:

╔═════╦═══════╗
║ id  ║ sets  ║
╠═════╬═══════╣
║  1  ║ 1     ║
║  2  ║ 1,2   ║
║  3  ║ 1,2,4 ║
║  4  ║ 1,2,6 ║
║  5  ║ 3,8,9 ║
╚═════╩═══════╝