mysql在多个表中查找不存在的记录

Question

    CREATE TABLE `Students` (
  `id` int(11) NOT NULL,
  `name` varchar(500) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `Students` (`id`, `name`) VALUES
(1, 'Student 1'), 
(2, 'Student 2'),
(3, 'Student 3');



CREATE TABLE `lessons` (
  `id` int(11) NOT NULL,
  `name` varchar(500) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `lessons` (`id`, `name`) VALUES
(1, 'Lesson 1'),
(2, 'Lesson 2'),
(3, 'Lesson 3');



CREATE TABLE `completed` (
  `student` int(11) NOT NULL,
  `lesson` varchar(500) NOT NULL,
  `completed` int(11) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO `completed` (`student`, `lesson`, `completed`) VALUES
(1, 2, 1),
(3, 3, 1),
(2, 1, 1);

我们正在将完成类(class)的学生添加到“已完成”表中。我们需要让 5 名学生的类(class) ID 不存在于“已完成”表中。

示例输出是；

1, 1
1, 3
2, 2
2, 3
3, 1
3, 2

谢谢

Answer 1

您可以使用NOT EXISTS

SELECT s.Id, s.Name
FROM Students s
WHERE NOT EXISTS (
    SELECT 1
    FROM Completed
    WHERE s.Id = student
)