oracle 反联结 NOT IN、NOT EXISTS

最新推荐文章于 2025-06-25 21:32:20 发布

原创最新推荐文章于 2025-06-25 21:32:20 发布 · 756 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

oracle

当前文章被收录于：

oracle

103 篇文章 2 人学习

订阅专栏查看详情

当前文章被以下社区和专栏收录：

本文通过具体的SQL示例对比了NOT IN与NOT EXISTS在处理NULL值时的不同行为，并提供了几种解决NOT IN处理NULL问题的方法。

用SCOTT/TIGER登录。

创建小表：

CREATE TABLE EMP1 AS WITH TEMP_EMP AS (SELECT ROWNUM RN, E.* FROM EMP E) SELECT * FROM TEMP_EMP WHERE RN BETWEEN 4 AND 10;

使用NOT IN：

SELECT EMPNO, ENAME FROM EMP WHERE EMPNO NOT IN (SELECT EMPNO FROM EMP1);

结果：

使用NOT EXISTS：

SELECT T1.EMPNO, T1.ENAME FROM EMP T1 WHERE NOT EXISTS (SELECT EMPNO FROM EMP1 T2 WHERE T1.EMPNO = T2.EMPNO);

结果和NOT IN一样。

遇到NULL值时：

NOT IN：

SELECT EMPNO, ENAME FROM EMP WHERE EMPNO NOT IN (SELECT NULL FROM EMP1);

结果：

NOT EXISTS：

SELECT T1.EMPNO, T1.ENAME FROM EMP T1 WHERE NOT EXISTS (SELECT NULL FROM EMP1 T2 WHERE T1.EMPNO = T2.EMPNO);

结果：

NOT IN和NOT EXISTS处理NULL的时候，是不一样的。

所以，最好使用NOT EXISTS，但是使用NOT IN比较易于理解。

如果使用NOT IN，可以使用如下几种方法解决NULL的问题：

1、使用NVL函数：

SQL> SELECT MGR FROM EMP WHERE MGR NOT IN (SELECT MGR FROM EMP1);

未选定行

SQL> SELECT MGR FROM EMP WHERE MGR NOT IN (SELECT NVL(MGR, 0) FROM EMP1);

       MGR
----------
      7902
      7788
      7782

SQL>

说明：NVL (expr1, expr2)->expr1为NULL，返回expr2；不为NULL，返回expr1。注意两者的类型要一致。

2、使用IS NOT NULL：

SQL> SELECT MGR FROM EMP WHERE MGR NOT IN (SELECT MGR FROM EMP1);

未选定行

SQL> SELECT MGR FROM EMP WHERE MGR NOT IN (SELECT MGR FROM EMP1 WHERE MGR IS NOT NULL);

       MGR
----------
      7902
      7788
      7782

SQL>

3、使用NOT NULL约束。