有效地选择在另一个表中缺少的记录 [英] Efficient way to select records missing in another table

问题描述

我有3个表。以下是结构：

I have 3 tables. Below is the structure:

• student （ id int ，name varchar（20））


• 课程（ course_id int，subject varchar（10））


• student_course （ st_id int，course_id int ） - >包含注册课程的学生姓名

• student (id int, name varchar(20))
• course (course_id int, subject varchar(10))
• student_course (st_id int, course_id int) -> contains name of students who enrolled for a course

现在，找出没有报名参加任何课程的学生。正如我可以想出的，有多种方法来获取这些信息。你能否让我知道哪一个是最有效的，也是为什么。此外，如果有其他更好的执行方式，请告诉我。

Now, I want to write a query to find out students who did not enroll for any course. As I could figure out there are multiple ways to fetching this information. Could you please let me know which one of these is the most efficient and also, why. Also, if there could be any other better way of executing same, please let me know.

db2 => select distinct name from student inner join student_course on id not in (select st_id from student_course)

db2 => select name from student minus (select name from student inner join student_course on id=st_id)

db2 => select name from student where id not in (select st_id from student_course)

p>

推荐答案

使用的子查询，无论是不在， minus 或其他，一般效率不高。常见的方式是 left join ：

The subqueries you use, whether it is not in, minus or whatever, are generally inefficient. Common way to do this is left join:

select name 
from student 
left join student_course on id = st_id
where st_id is NULL

使用 join 是正常和优先解决方案。

Using join is "normal" and preffered solution.

这篇关于有效地选择在另一个表中缺少的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！