mysql not in 子查询

1. 什么是 not in 子查询

在 MySQL 中，not in 是一种条件判断语句，用于根据子查询的结果来过滤数据。not in 子查询可以用来筛选出在子查询结果集中不存在的记录。

2. not in 的语法

SELECT column1, column2, ...
FROM table_name
WHERE column_name NOT IN (subquery);

column1, column2, …：要查询的列名
table_name：要查询的表名
column_name：要进行条件判断的列名
subquery：子查询，用于筛选出要排除的数据

3. not in 的使用示例

假设有两张表 students 和 absentees，分别记录所有的学生和缺勤的学生。我们需要查询出没有缺勤记录的学生。

3.1 创建测试表

CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE absentees(
  id INT PRIMARY KEY AUTO_INCREMENT,
  student_id INT,
  FOREIGN KEY (student_id) REFERENCES students(id)
);

INSERT INTO students (name) VALUES ('Alice'), ('Bob'), ('Charlie'), ('David');

INSERT INTO absentees (student_id) VALUES (1), (3);

3.2 使用 not in 子查询

SELECT *
FROM students
WHERE id NOT IN (SELECT student_id FROM absentees);

运行以上查询，可以得到以下结果：

id	name
2	Bob
4	David

从结果中可以看出，Alice 和 Charlie 这两个学生在 absentees 表中有缺勤记录，而 Bob 和 David 没有缺勤记录。

4. 注意事项

not in 子查询适用于子查询结果不为空的情况。如果子查询结果为空，not in 将永远返回空结果集。
如果子查询结果集中含有 NULL 值，not in 将返回空结果集。这是因为 NULL 无法与其他值进行比较，所以无法确定是否相等。

5. not in 和 not exists 的区别

not in 和 not exists 都可以用于实现相同的功能，即过滤出在子查询结果集中不存在的数据。但两者之间有一些细微的差别。

not in 使用示例：

SELECT *
FROM table1
WHERE column1 NOT IN (SELECT column2 FROM table2);

not exists 使用示例：

SELECT *
FROM table1
WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.column1 = table2.column2);

子查询中的列不同：
- not in 子查询中，通常选择表中的某一列。
- not exists 子查询中，通常使用 * 代表所有列。
性能方面：
- 如果子查询结果集较大，not exists 的性能通常更好，因为它只需要判断是否存在记录即可，而不需要返回具体的数据。
- 如果子查询结果集较小，not in 的性能可能更好，因为它可以直接将子查询结果与主查询的条件进行比较，不需要进行索引查找。
处理 NULL 值：
- not in 子查询中，如果子查询结果集含有 NULL 值，则无法比较，会返回空结果集。
- not exists 子查询中，能够正确处理包含 NULL 值的情况。

6. 总结

not in 子查询是 MySQL 中的一种条件判断语句，用于根据子查询结果集来过滤数据。通过使用 not in 子查询，我们可以轻松地筛选出在子查询结果集中不存在的记录。在实际使用中，我们需要注意 not in 子查询的使用场景、处理 NULL 值的方式，以及与 not exists 的区别等方面的细节。