MySQL查询根据不在另一个表中的特定键从表中选择行

Question

如何按 ID 和标题(从“考试”表中)选择学生(在“成绩”表中)尚未撰写的所有考试？

表:成绩

+--------------+----+----
|student_number|e_id| ...
+--------------+----+----
|888075        |1   | ...
|888075        |2   | ...
|888075        |4   | ...
|637020        |2   | ...
+--------------+----+----

表:考试

+----+------+
|e_id|title |
+----+------+
|1   |exam 1|
|2   |exam 2|
|3   |exam 3|
|4   |exam 4|
+----+------+

在这种特殊情况下，我希望学生 888075 得到以下输出:

+--+------+
|id|title |
+--+------+
|3 |exam 3|
+--+------+

我只需要这个的逆选择:

SELECT e.e_id as id, e.title as title
FROM grades g
LEFT JOIN exams e
ON g.e_id = e.e_id
WHERE g.student_number = '888075'

Answer 1

您的查询已接近 - 只需反转连接并相应地检查 null:

SELECT e.e_id as id, e.title as title
FROM exams e
   LEFT JOIN grades g ON g.e_id = e.e_id
      AND g.student_number = '888075'
WHERE g.e_id IS NULL