Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


create table students (
id int,
name varchar(10)
);

create table courses (
id int,
name varchar(100)
);

create table student_course_mapping (
studentid int,
courseid int
);

insert into students values
(1,	'Shibbir'),
(2,	'Ahmed'),
(3,	'John');

insert into courses values
(1,	'Data Structure'),
(2,	'Algorithms'),
(3,	'Math');

insert into student_course_mapping values
(1,	1),
(1,	2),
(1,	3),
(2,	2),
(2,	3),
(3,	1),
(3,	2);
3 rows affected

3 rows affected

7 rows affected

SELECT 
    input.value,
    s.studentid
FROM (VALUES (ARRAY[2, 1, 3]), (ARRAY[2, 3]), (ARRAY[1, 2])) input(value)
JOIN (
    SELECT 
        studentid,
        ARRAY_AGG(courseid) course_ids
    FROM student_course_mapping 
    GROUP BY studentid
) s ON s.course_ids @> input.value AND ARRAY_LENGTH(s.course_ids, 1) = ARRAY_LENGTH(input.value, 1)


value
studentid


{1,2}
3

{2,3}
2

{2,1,3}
1