IN & NOT IN & EXISTS & NOT EXISTS & TABLE JION

最新推荐文章于 2023-02-08 22:51:08 发布

转载最新推荐文章于 2023-02-08 22:51:08 发布 · 98 阅读

本文探讨了在SQL查询中使用NOT IN时遇到NULL值的问题，并对比了NOT IN与NOT EXISTS的区别。通过实例展示了如何避免因NULL导致的查询错误，并介绍了如何通过EXISTS或NOT EXISTS替代IN与NOT IN。

当子查询有返回null值时，not in不会有结果返回，如：

SQL> select ename from emp where deptno not in(10,20,null)
2 /

no rows selected

原因是：

deptno not in(10, 20, null) ===>

not (deptno in(10, 20, null)) ===>

not (deptno=10 or deptno=20 or deptno=null) ===>

deptno<>10 and deptno<>20 and deptno<>null ===>

deptno<>10 and deptno<>20 and unkown ===>

deptno<>10 and deptno<>20 and false ===>false

下面是两个在NOT IN中使用子查询的例子，先看没有null的情况：

SQL> select ename, deptno from emp;

ENAME DEPTNO
---------- ----------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10

14 rows selected.

SQL> select dname from dept where deptno not in(select deptno from emp);

DNAME
--------------
OPERATIONS

再看子查询包含null值的情况：

SQL> insert into emp(empno, ename,deptno)
2 values(9999,'LAW',null)
3 /

1 row created.

SQL> select ename, deptno from emp;

ENAME DEPTNO
---------- ----------
LAW
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10

15 rows selected.
SQL> select dname from dept where deptno not in(select deptno from emp);

no rows selected

SQL> select dname from dept
2 where deptno not in(select deptno from emp where deptno is not null)
3 /

DNAME
--------------
OPERATIONS

而子查询包含null时，用in却不会有问题：

SQL> select ename,deptno from emp where deptno in(10,20,null)
2 /

ENAME DEPTNO
---------- ----------
SMITH 20
JONES 20
CLARK 10
SCOTT 20
KING 10
ADAMS 20
FORD 20
MILLER 10

8 rows selected.

IN一般可以改为EXISTS：

SQL> select dname from dept
2 where deptno in(select deptno from emp)
3 /

DNAME
--------------
RESEARCH
SALES
ACCOUNTING

SQL> select dname from dept
2 where exists(select * from emp where emp.deptno=dept.deptno)
3 /

DNAME
--------------
RESEARCH
SALES
ACCOUNTING

而NOT IN一般可以改为NOT EXISTS：

SQL> select dname from dept
2 where deptno not in(select deptno from emp where deptno is not null)
3 /

DNAME
--------------
OPERATIONS

SQL> select dname from dept
2 where not exists(select * from emp where dept.deptno=emp.deptno)
3 /

DNAME
--------------
OPERATIONS

使用NOT EXISTS，即使子查询中包含NULL值，也会得到正确结果。原因是：

select * from emp where dept.deptno=null不会有返回值，这样，EXISTS(select * from emp where dept.deptno=null)返回的布尔值为false，而 NOT(false)显然为TRUE，其他条件与之作and后，依然得到true。

而在NOT IN子句中，是NOT(deptno=null)，即NOT(unkown)，结果依然为unkown，而unkown被当作false，其他条件与之作and后，最后得到false。

NOT EXISTS(select * from emp where dept.deptno=null) ===>

NOT (false) ===>TRUE

所以子查询中的null不会影响其他的查询结果。

EXISTS可以改为使用表连接语法：

SQL> select dname from dept
2 where exists(select * from emp where emp.deptno=dept.deptno)
3 /

DNAME
--------------
RESEARCH
SALES
ACCOUNTING

SQL> select distinct dname from dept,emp
2 where dept.deptno=emp.deptno
3 /

DNAME
--------------
ACCOUNTING
RESEARCH
SALES

修改为表连接语法后，性能会有所提高，而且可以在select子句中查询另外一个表中的列。

EXISTS还可以改为使用count(*)，因为这时对于每个dept表中的deptno要遍历整个emp表，很明星性能会差：

SQL> select dname from dept
2 where (select count(*) from emp where emp.deptno=dept.deptno)>0
3 /