How to perform IS_THE_SUBSET_OF function in MySQL?

Question

Added: the CREATE code for student and takes

CREATE TABLE `student` (
    `sid` VARCHAR(6) NOT NULL,
    `sname` VARCHAR(6) NULL DEFAULT NULL,
    `sex` VARCHAR(2) NULL DEFAULT NULL,
    `age` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
    `dept` VARCHAR(50) NULL DEFAULT NULL,
    `class` VARCHAR(4) NULL DEFAULT NULL,
    PRIMARY KEY (`sid`)
);

CREATE TABLE `takes` (
    `sid` VARCHAR(6) NOT NULL,
    `cid` VARCHAR(3) NOT NULL,
    `score` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
    PRIMARY KEY (`sid`, `cid`)
)

I am going to display the IDs and names of all students who have taken the courses which are taken by the student whose ID is '31401'. I have the following code:

SELECT sid, sname
FROM student S
WHERE NOT EXISTS((SELECT cid
                 FROM takes
                 WHERE sid = '31401')
                 EXCEPT
                 (SELECT cid
                 FROM takes T
                 WHERE S.sid = T.sid));

However, there is no EXCEPT operation in MySQL. So I wonder if I could rewrite like this:

SELECT sid, sname
FROM student S
WHERE ((SELECT cid
        FROM takes
        WHERE sid = '31401')
        IS_THE_SUBSET_OF
       (SELECT cid
        FROM takes T
        WHERE S.sid = T.sid));

How can I implement the IS_THE_SUBSET_OF function?

you can use HAVING, IS, IS NOT, IS NOT IN() etc or you could just use joins to do what you want instead save the sub queries and be much quicker. — Dave, Apr 16 '18 at 11:27
Possible duplicate of [Error when using except in a query](https://stackoverflow.com/questions/16092353/error-when-using-except-in-a-query) — Raymond Nijland, Apr 16 '18 at 11:27

score 1 · Accepted Answer · answered Apr 16 '18 at 11:27

There is no set-based operator that does what you want. You can use join, group by and some other logic:

select t.sid
from takes t join
     takes ts
     on t.cid = ts.cid and ts.sid = 31401  -- don't use single quotes for a numeric constant
group by t.sid
having count(*) = (select count(*) from takes ts2 where ts2.sid = 31401);

This formulation assumes that takes does not have duplicate sid/cid pairs.

How to perform IS_THE_SUBSET_OF function in MySQL?

1 Answers1