SQL中EXCEPT和Not in的区别？

最新推荐文章于 2025-03-04 14:52:32 发布

u010227447

最新推荐文章于 2025-03-04 14:52:32 发布

阅读量824

点赞数

CC 4.0 BY-SA版权

文章标签： java select

本文链接：https://blog.youkuaiyun.com/u010227447/article/details/22849085

本文通过具体的SQL示例对比了EXCEPT和NOT IN操作符的区别，尤其是在处理NULL值时的不同表现。EXCEPT会去除重复并比较所有列，而NOT IN则不会，除非在查询中明确指定。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

初始化两张表：
CREATE TABLE tb1(ID int)
INSERT tb1          SELECT NULL
UNION ALL          SELECT NULL
UNION ALL          SELECT NULL
UNION ALL          SELECT 1
UNION ALL          SELECT 2
UNION ALL          SELECT 2
UNION ALL          SELECT 2
UNION ALL          SELECT 3
UNION ALL          SELECT 4
UNION ALL          SELECT 4

CREATE TABLE tb2(ID int)
INSERT tb2        SELECT NULL
UNION ALL          SELECT 1
UNION ALL          SELECT 3
UNION ALL          SELECT 4
UNION ALL          SELECT 4
A:
SELECT * FROM tb1
SELECT * FROM tb2

SELECT * FROM tb1 EXCEPT SELECT * FROM tb2;
SELECT * FROM tb1 WHERE id NOT IN(SELECT id FROM tb2);--得不到任何值
结果：
B、我先删除表tb1的是NULL值的行
--DELETE FROM tb1 where id is null
B、
SELECT * FROM tb1 EXCEPT SELECT * FROM tb2;
SELECT * FROM tb1 WHERE id NOT IN(SELECT id FROM tb2);--得不到任何值
结果：同上A
C、把表tb2的是NULL值的行也删除
--DELETE FROM tb2 where id is null
C、
SELECT * FROM tb1 EXCEPT SELECT * FROM tb2;
SELECT * FROM tb1 WHERE id NOT IN(SELECT id FROM tb2);
结果：
这是两张表中都没有NULL值时，得到的结果；
D、在tb1表中插入一条NULL值
D、

SELECT * FROM tb1 EXCEPT SELECT * FROM tb2;
SELECT * FROM tb1 WHERE id NOT IN(SELECT id FROM tb2);
结果：
以上例子说明：
except会去重复, not in 不会(除非你在select中显式指定)
except用于比较的列是所有列, 除非写子查询限制列, not in 没有这种情况
表tb2中如果有null值的话，not in查询得不到值（如：A、B）
表tb1中如果有null值，not in不会查询出这个null值（如：D），而except可以查询到
当然通过对子查询指定不为NULL的话，NOT IN自然会得到值，如：
SELECT * FROM tb1 WHERE id NOT IN(SELECT id FROM tb2 WHERE ID IS NOT NULL);