Belonging to a certain set - and not to any other [sql]

Question

MySQL

Hello habr.

I boiled my head a little, maybe a simple task, but nothing comes to mind.

We have Sets X, for example (1,2; 2,3,4; 7,9,1; 1,0), we have a set Y, for example (1,2,3,4,5,6,7,8 )
Is it possible to show from X only that which is fully present in Y? In the example, only (1,2; 2,3,4) is suitable.

If, in a simple way, the IN construction does the same, but not for each member (1 is in Y, which means it satisfies the condition), but throughout the collection.
Pseudo syntax:

SELECT t1.id FROM table1 t1, table2 t2 WHERE t2.key NOT FULLY IN(SELECT id FROM table3)

Answer 1

Let's start by understanding that X is a set of sets and Y is a set of elements. Therefore, you can (and should) compare Y and elements of X, or elements of Y and “elements of elements” of X.
Further, we denote the element of X as x. The fact that x is a subset of Y can be expressed in SQL as

not exists (
    select 1
      from x left join Y on x.id = Y.id
     where Y.id is null
)

Further, returning to your task, you need to clarify how you store the set X. For example, if there is X (set_id, element_id) in one table, then you can join it with Y with an outer join, as above, and then filter out the sets that are not completely contained in Y. Analytic functions are good for this, if your DBMS supports them properly. Again, clarification is required.

Answer 2

Maybe something like this will do:
select t1.id from table t1 where t1.key NOT in (subquery transposing column to row using SYS_CONNECT_BY_PATH...) ???
what I won’t write in parentheses right away, you need to remember :)