mysql中not in 和 not exists不是等价的
参考网址:
https://blog.csdn.net/yj_445324989/article/details/102576518?ops_request_misc=%25257B%252522request%25255Fid%252522%25253A%252522160959460916780262042949%252522%25252C%252522scm%252522%25253A%25252220140713.130102334.pc%25255Fall.%252522%25257D&request_id=160959460916780262042949&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2allfirst_rank_v2~rank_v29-8-102576518.first_rank_v2_pc_rank_v29&utm_term=ALL%20%E8%B0%93%E8%AF%8D%E7%94%A8%E4%BA%8E%20NULL%20%E6%88%96%E7%A9%BA%E9%9B%86%E6%97%B6%E4%BC%9A%E5%87%BA%E7%8E%B0%E9%97%AE%E9%A2%98
说明:
sql中查询重要的三个点
1.子查询的
exists,in,not in , not exists
2.连接查询
3.null值的处理
以后准备整理一下这三个点,总结好
1.准备数据库脚本
CREATE TABLE Class_A
(name VARCHAR(16) PRIMARY KEY,
age INTEGER,
city VARCHAR(16) NOT NULL );
CREATE TABLE Class_B
(name VARCHAR(16) PRIMARY KEY,
age INTEGER,
city VARCHAR(16) NOT NULL );
INSERT INTO Class_A VALUES('布朗', 22, '东京');
INSERT INTO Class_A VALUES('拉里', 19, '埼玉');
INSERT INTO Class_A VALUES('伯杰', 21, '千叶');
INSERT INTO Class_B VALUES('齐藤', 22, '东京');
INSERT INTO Class_B VALUES('田尻', 23, '东京');
INSERT INTO Class_B VALUES('山田', NULL, '东京');
INSERT INTO Class_B VALUES('和泉', 18, '千叶');
INSERT INTO Class_B VALUES('武田', 20, '千叶');
INSERT INTO Class_B VALUES('石川', 19, '神奈川');
2.比较not in 和 not exists
我们考虑查询”与B班住在东京的学生年龄不同 的A班学生“。也就是说我们希望查询到的结果是拉里与伯杰
not in 实现
mysql> select * from class_a
-> where age not in (select age from class_b where city='东京');
Empty set (0.04 sec)
not exists 实现
mysql> select * from class_a a
-> where not exists (select * from class_b b where a.age=b.age and b.city='东京');
+--------+------+--------+
| name | age | city |
+--------+------+--------+
| 伯杰 | 21 | 千叶 |
| 拉里 | 19 | 埼玉 |
+--------+------+--------+
3.结论
1.in改写成exists是等价的
2.not in 子句查询中用到的表里被选择的列中存在null,则SQL语句整体的查询结果永远是空,not in和not exists不是等价的