16 反连接(anti-join)--优化主题系列
反连接(anti-join)
反连接其实是特殊的半连接。只是把in/exists换成了notin/not exists
执行计划中,看到有NESTED LOOPS ANTI/HASH JOIN ANTI 就表示有反连接
举个例子(基于HROracle11gR2)下面有2个SQL:
select department_name
from hr.departments dept
where department_id NOT IN
(select department_id from hr.employees emp);
select department_name
from hr.departments dept
where NOT EXISTS (select null from hr.employees emp
where emp.department_id = dept.department_id);
以上两个SQL不等价
NOT IN执行计划是filter对吧??
NOT EXISTS就是正常的JOIN方式了吧
令人惊讶的是,not in不返回结果,notexists返回16行。
这里也说明not in和notexists不能像in和exists那样随意改写SQL。
为什么用not in不返回结果呢??因为子查询
select department_id from hr.employees emp
会返回NULL值。Oracle有个缺陷:
in里面有NULL值会返回结果,比如下面SQL
select department_name from hr.departments dept wheredepartment_id in(10,50,null);
单not in 里面有NULL值就不会返回结果,直接返回NULL
select department_name from hr.departments dept wheredepartment_id not in(10,50,null);
我们在做SQL优化的时候,一定要注意not in和notexists是不是能等价转换。
当然了,一般情况下,notin子查询里面都会排除有NULL的情况,不然查询结果没意义。
现在来过滤掉NULL值。
A LEFT JOIN B 是不是说A有并且B没有 B用NULL代替
那我加个where条件 IS NULL
B.XXX IS NULL 是不是说明A和B 没关联上??
是不是等效于 NOTIN,NOT EXISTS的效果
总结一下in/exists,notin/not exists 一般情况下,当SQL很简单,他们的执行计划是一样的,也就是说他们的性能时一样的。但是SQL一复杂了,他们的执行计划就可能不一样,这个时候就要我们去解决这些问题。这里,我不会给你们讲什么情况下应该用notin什么情况下应该用notexists,因为这些理论是没用的,要具体情况具体分析。后面的章节我会讲解半连接和反连接最底层的原理,把最底层原理搞懂之后,大家以后在遇到in和exists,not in和notexists就会迎刃而解了。