query performance – MySQL compare multiple values in WHERE clause, find subset

I have a table segments with column comp_id, segment_id.

comp_id: 1
segment_id: 23,11,223,3331;
comp_id: 2
segment_id: 231,11,223,3331;

I have another table: comps with segment_id as well.

comp_id: 1
segment_id: 11, 3331
comp_id: 3
segment_id: 7, 63

I need to filter out rows from comp where all segment_id values are inside segment_id from segments table.

This is a simplified version of what I need to do. simple JOIN statements wouldn’t help, I need to do this inside WHERE clause.


comp.segment_id = ALL (SELECT segment_id FROM segments)

this won’t work because this compares single “comp.segment_id”, I need all “comp.segment_id”s compared.

Is there any function or some method of doing this? And btw performance is crusial.
Is there any intersect methods to do so?
To simplify I need to find subsets of data.
Actual query is very big and I won’t post it here, don’t give me JOIN examples, please.