MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.25


CREATE TABLE pivot (
   id     int AUTO_INCREMENT PRIMARY KEY
 , tags   varchar(255)
);
INSERT INTO pivot (tags) VALUES
   ('tag1,tag2,tag3,tag1')
 , ('tag2')
 , ('tag4,tag5,tag4,tag4')
 , ('tag5,tag5,tag4')
 , ('tag5,tag5,tag4')
 , ('tag1,tag3,tag2,tag1')
;
WITH RECURSIVE seq (n) AS (
            SELECT 1
             UNION ALL
            SELECT n + 1 FROM seq WHERE n <= 9
       )
SELECT DISTINCT t1.*
     , REPLACE(TRIM(LEADING SUBSTRING_INDEX(t1.tags,',',seq.n-1) FROM SUBSTRING_INDEX(t1.tags,',',seq.n)), ',','') AS tag
  FROM pivot AS t1
  JOIN seq
    ON seq.n > 0 AND SUBSTRING_INDEX(t1.tags,',',seq.n-1) <> SUBSTRING_INDEX(t1.tags,',',seq.n)
 ORDER BY id, tag
;


id
tags
tag


1
tag1,tag2,tag3,tag1
tag1

1
tag1,tag2,tag3,tag1
tag2

1
tag1,tag2,tag3,tag1
tag3

2
tag2
tag2

3
tag4,tag5,tag4,tag4
tag4

3
tag4,tag5,tag4,tag4
tag5

4
tag5,tag5,tag4
tag4

4
tag5,tag5,tag4
tag5

5
tag5,tag5,tag4
tag4

5
tag5,tag5,tag4
tag5

6
tag1,tag3,tag2,tag1
tag1

6
tag1,tag3,tag2,tag1
tag2

6
tag1,tag3,tag2,tag1
tag3


-- Given the above normalized list, we can then find if some given set
-- is a subset of a stored set:
WITH RECURSIVE seq (n) AS (
            SELECT 1
             UNION ALL
            SELECT n + 1 FROM seq WHERE n <= 9
       )
   , norm AS (
            SELECT DISTINCT t1.*
                 , REPLACE(TRIM(LEADING SUBSTRING_INDEX(t1.tags,',',seq.n-1) FROM SUBSTRING_INDEX(t1.tags,',',seq.n)), ',','') AS tag
              FROM pivot AS t1
              JOIN seq
                ON seq.n > 0 AND SUBSTRING_INDEX(t1.tags,',',seq.n-1) <> SUBSTRING_INDEX(t1.tags,',',seq.n)
     )
SELECT id
     , tags
  FROM norm
 WHERE tag IN ('tag2', 'tag3')
 GROUP BY id
HAVING COUNT(DISTINCT tag) = 2
 ORDER BY id, tags
;


id
tags


1
tag1,tag2,tag3,tag1

6
tag1,tag3,tag2,tag1


-- Given the above normalized list, we can then find some given set
-- is a superset (or match) of a stored set:
WITH RECURSIVE seq (n) AS (
            SELECT 1
             UNION ALL
            SELECT n + 1 FROM seq WHERE n <= 9
       )
   , norm AS (
            SELECT DISTINCT t1.*
                 , REPLACE(TRIM(LEADING SUBSTRING_INDEX(t1.tags,',',seq.n-1) FROM SUBSTRING_INDEX(t1.tags,',',seq.n)), ',','') AS tag
              FROM pivot AS t1
              JOIN seq
                ON seq.n > 0 AND SUBSTRING_INDEX(t1.tags,',',seq.n-1) <> SUBSTRING_INDEX(t1.tags,',',seq.n)
     )
SELECT id
     , tags
  FROM norm
 GROUP BY id
HAVING COUNT(DISTINCT CASE WHEN tag IN ('tag2', 'tag3', 'tag8', 'tag9') THEN tag END) = COUNT(DISTINCT tag)
 ORDER BY id, tags
;


id
tags


2
tag2


truncate pivot;
INSERT INTO pivot (tags) VALUES 
    ('A,B')
  , ('G,K,E')
  , ('A,B,I')
;
WITH RECURSIVE seq (n) AS (
            SELECT 1
             UNION ALL
            SELECT n + 1 FROM seq WHERE n <= 9
       )
   , norm AS (
            SELECT DISTINCT t1.*
                 , REPLACE(TRIM(LEADING SUBSTRING_INDEX(t1.tags,',',seq.n-1) FROM SUBSTRING_INDEX(t1.tags,',',seq.n)), ',','') AS tag
              FROM pivot AS t1
              JOIN seq
                ON seq.n > 0 AND SUBSTRING_INDEX(t1.tags,',',seq.n-1) <> SUBSTRING_INDEX(t1.tags,',',seq.n)
     )
SELECT id
     , tags
  FROM norm
 GROUP BY id
HAVING COUNT(DISTINCT CASE WHEN tag IN ('A','B','C','D','E','F','G') THEN tag END) = COUNT(DISTINCT tag)
 ORDER BY id, tags
;


id
tags


1
A,B

id	tags	tag
1	tag1,tag2,tag3,tag1	tag1
1	tag1,tag2,tag3,tag1	tag2
1	tag1,tag2,tag3,tag1	tag3
2	tag2	tag2
3	tag4,tag5,tag4,tag4	tag4
3	tag4,tag5,tag4,tag4	tag5
4	tag5,tag5,tag4	tag4
4	tag5,tag5,tag4	tag5
5	tag5,tag5,tag4	tag4
5	tag5,tag5,tag4	tag5
6	tag1,tag3,tag2,tag1	tag1
6	tag1,tag3,tag2,tag1	tag2
6	tag1,tag3,tag2,tag1	tag3