用sql语句写出没有选修某门课的学生

来源：百度知道 编辑：UC知道 时间：2024/05/14 17:37:44
表student(id, name)
表elective (course_id, student_id)
表course(id, name)

学生和课程是多对多关系

要求查出没有选修某门课的学生，假设这门课course_id 是5
怎么写出sql，谢谢

select * from student a where not exists
(select 'x' from elective where student_id = a.id and course_id = '5')

select * from student where id not in
(select student_id from elective where course_id = '5')

第一种写法的效率要比第二种高

SELECT name FROM student WHERE id NOT IN
(SELECT student_id FROM elective WHERE course_id IN
(SELECT id FROM course WHERE course_id=5)
);

select name from student where not exists(select * from elective where student_id=id and course_id=5)

select id, name from student where id not in
(select student_id from elective where course_id =5)

select name from student where id not in
(select student_id from elective where course_id =5)

select student.name from course where course_id=5