Conditionally selecting elements of a CSV column value in MySQL

I have two tables, targets and rulesets. The rulesets table contains a column target_ids that is a CSV of primary key IDs into targets, and I want a SELECT to return any elements in the CSV field for a particular row have lost referential integrity to the targets table.

Is there any way to accomplish this without needing to create a temporary table?

I’m aware of some solutions to this issue that will extract the CSV values into a temporary table, such as this answer: https://stackoverflow.com/a/17043084/342692, but I’m trying to avoid a temporary table due to permission restrictions. I’m also aware of FIND_IN_SET being useful to compare a given value to a CSV list, but it doesn’t seem to help in this case.

Here’s an example of the schema and the goal, and a link to DB fiddle with the same content:

CREATE TABLE IF NOT EXISTS `targets` (
  `id` int(6) unsigned,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `rulesets` (
  `id` int(6) unsigned,
  `target_ids` text,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `targets` VALUES
  (1),
  (2),
  (3);

INSERT INTO `rulesets` VALUES
  (1, '2,3,4');
-- what targets exist in `targets` table but not in the CSV ruleset?
-- returns [1]
select t.id from targets t where find_in_set(t.id, (select target_ids from rulesets where id=1)) = 0;

-- what targets exist in both the `targets` table and the CSV ruleset?
-- returns [2,3]
select t.id from targets t where find_in_set(t.id, (select target_ids from rulesets where id=1)) > 0;

-- what targets do not exist in `targets` table but are in the CSV ruleset?
-- returns [4]
-- ??? 

Answer

Here’s one idea, using a simple utility table (ints) of integers (i) from 0-9…

-- what targets exist in `targets` table but not in the CSV ruleset?

SELECT a.id target_id 
  FROM targets a
  LEFT
  JOIN
     ( SELECT DISTINCT r.id
                     , SUBSTRING_INDEX(SUBSTRING_INDEX(r.target_ids,',',i.i+1),',',-1) target_id
                  FROM rulesets r
                  JOIN ints i
     ) b
    ON b.target_id = a.id
 WHERE b.id IS NULL;


-- what targets exist in both the `targets` table and the CSV ruleset?

SELECT a.id target_id
  FROM targets a
  JOIN
     ( SELECT DISTINCT r.id
                     , SUBSTRING_INDEX(SUBSTRING_INDEX(r.target_ids,',',i.i+1),',',-1) target_id
                  FROM rulesets r
                  JOIN ints i
     ) b
    ON b.target_id = a.id;
    
-- what targets do not exist in `targets` table but are in the CSV ruleset?
SELECT b.target_id 
  FROM targets a
  RIGHT
  JOIN
     ( SELECT DISTINCT r.id
                     , SUBSTRING_INDEX(SUBSTRING_INDEX(r.target_ids,',',i.i+1),',',-1) target_id
                  FROM rulesets r
                  JOIN ints i
     ) b
    ON b.target_id = a.id
 WHERE a.id IS NULL;

For the this last query, normally, you’d reverse the order of the tables above so as to avoid using a RIGHT JOIN, but I’ve written it this way to highlight its similarity to the first query