如何在MySQL查询中组合JOIN和NOT EXISTS

Question

我有三个表，它们各自有行。

all_students     all_courses    courses_students
------------     -----------    ----------------
id               id             student_id (foreign key from all_students.id)
name             name           course_id (foreign key from all_courses.id)

当学生注册课程时，他们的id将与课程id一起存储在courses_students表中。到目前为止，我可以使用以下查询检索所有学生的姓名和课程：

SELECT s.id    AS studentId,
s.name  AS studentName,
c.id    AS courseId,
c.name  AS courseName,
FROM   courses_students AS cs
JOIN   all_students     AS s ON s.id = cs.student_id
JOIN   all_courses      AS c ON c.id = cs.course_id;

如何修改上述查询，以便向未注册任何课程的学生显示？

Answer 1

使用左联接，而不是查询当前使用的(默认(内部联接：

SELECT
s.id AS studentId,
s.name AS studentName,
COALESCE(c.id, 'NA') AS courseId,
COALESCE(c.name, 'NA') AS courseName
FROM all_students s
LEFT JOIN courses_students AS cs
ON s.id = cs.student_id
LEFT JOIN all_courses AS c
ON c.id = cs.course_id;

这里的关键点是使用左联接，这样如果学生不匹配任何课程，就不会被退学。此外，我们从all_students表开始查询，然后从中加入。

Answer 2

将student-to-studentcourses联接更改为LEFT join，并在联接列上查找null：

SELECT s.id    AS studentId,
s.name  AS studentName,
c.id    AS courseId,
c.name  AS courseName,
FROM  
all_students s 
LEFT JOIN courses_students cs ON cs.student_id = s.id 
LEFT JOIN all_courses c ON c.id = cs.course_id
WHERE cs.student_id IS NULL

左联接将左侧的表(在单词"联接"之前提到的表——书写顺序很重要，我们倾向于重新排列查询，始终使用左联接而不是右联接，因为数据库按从左到右的顺序处理联接(作为实体表，其行必须全部出现在输出中，并尝试将右表中的行与它们匹配。如果找不到匹配项，它就会放弃，并将表的所有null放在右边，而不是实际数据。他的意思是，右边的桌子可能会在没有对手的数据中出现漏洞，因此我认为左边是"洞"；固体"；右侧为"0"；斑片状/稀疏/多孔-y"；

s.id, cs.student_id
1     1
2     NULL

学生1有匹配项，学生2没有(学生课程中没有学生ID为2的行(

因为cs.student_id是联接的一部分，所以NULL出现在该列中的唯一方法是联接不成功(该学生没有课程(。。如果你使用左联接表中的其他列，它也可以工作，因为如果左联接找不到匹配的行，联接表中所有列都是null，但其他列可能自然包含null(就像还没有人决定课程的教室，所以数据只是null(作为数据的一部分，因此，通过在联接列中查找null，我们可以确保只得到联接不匹配的行。如果我们说"；其中courses.classroom为空"；它会发现没有课程的学生，但也有可能发现所有学生都被分配到一门还没有设置的课程

使用EXISTS看起来有点不同，你需要去掉连接，因为在那里没有任何意义/如果没有匹配的课程，它们会扼杀你的结果：

SELECT *    
FROM all_students s 
WHERE NOT EXISTS
(SELECT null FROM courses_students x WHERE x.student_id = s.id)

您可以设想，对于学生中的每一行，您的数据库都会获取id，将其替换为子查询中的s.id，然后运行子查询。如果不生成任何行，则该学生将被添加到结果中。转到下一个学生

Answer 3

您实际上并不需要查询中的cources:

SELECT s.id    AS studentId,
s.name  AS studentName,
FROM all_students     AS s
LEFT JOIN courses_students AS cs ON s.id = cs.student_id
GROUP BY s.id
HAVING COUNT(cs.student_id) = 0

因此，我们按学生分组，但匹配课程的计数为0。即，所有值都为NULL(通过LEFT JOIN(。