mysql - 选择记录不在记录集中

Question

student桌子

|----------------------|
| student_id | name    |
|------------|---------|
| 1          | Richard |
| 2          | Emily   |
| 3          | Hans    |
|------------|---------|

---

lecturer桌子

|--------------------|
| lecturer_id | name |
|-------------|------|
| 1           | John |
| 2           | Mike |
|-------------|------|

---

classes桌子

|-----------------------------------------------|
| class_id | lecturer_id | material             |
|----------|-------------|----------------------|
| 1        | 1           | Basic of algorithm   |
| 2        | 1           | Basic of programming |
| 3        | 2           | Database  Essentials |
| 4        | 2           | Basic of SQL         |
|----------|-------------|----------------------|

---

attendance桌子

|-----------------------|
| class_id | student_id |
|----------|------------|
| 1        | 1          |
| 1        | 2          |
| 1        | 3          |
| 2        | 1          |
| 2        | 2          |
| 3        | 1          |
| 3        | 2          |
| 3        | 3          |
| 4        | 1          |
| 4        | 2          |
|----------|------------|

---

如何在 MySQL 中显示 Hans（学生）未参加的课程记录（来自课程表）？

期望的结果：

|-----------------------------------------------|
| class_id | lecturer_id | material             |
|----------|-------------|----------------------|
| 2        | 1           | Basic of programming |
| 4        | 2           | Basic of SQL         |
|----------|-------------|----------------------|

标签： mysql

Answer 1

一种方法使用EXISTS：

SELECT c.class_id, c.lecturer_id, c.material
FROM classes c
WHERE NOT EXISTS (SELECT 1 FROM attendance a
                  INNER JOIN student s
                      ON a.student_id = s.student_id
                  WHERE a.class_id = c.class_id AND
                        s.name = 'Hans');