深入解析：半连接与反连接的原理和等价改写方法

数据和云

发布于 2018-03-29 14:54:37

1.8K0

发布于 2018-03-29 14:54:37

文章被收录于专栏：数据和云数据和云

半连接的原理及等价改写

1. 什么是半连接

当两张表进行关联，只返回匹配上的数据并且只会返回一张的表的数据，半连接一般就是指的在子查询中出现 IN 和 EXISTS。

执行计划最常见的有下面的两个：

NESTED LOOPS SEMI
HASH JOIN SEMI

1.1 NESTED LOOP SEMI

执行步骤：

优化器选择主查询中的表为驱动表；
选择子查询中的表为被驱动表；
把驱动表的每一行根据连接列去被驱动表中去查找匹配的行；
如果在被驱动表中匹配上一个或者多个，则返回驱动表中的数据。

HINT /*+ use_nl(b,a) leading(b) */

看下这个 SQL：

explain plan for Select a.deptno, a.dname from dept a where exists (select 1 from emp b where a.deptno =b.deptno);

执行计划：

1* select * from table(dbms_xplan.display) PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 3429995275 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 3 | 105 | 3 (0)| 00:00:01 | | 1| NESTED LOOPSSEMI | | 3 | 105 | 3 (0)| 00:00:01 | | 2| TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 | |* 3| INDEX RANGE SCAN | IDX_DEPTNO | 11 | 143 | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified byoperation id): --------------------------------------------------- 3- access("A"."DEPTNO"="B"."DEPTNO")

用 hint 换下驱动表：

跑下这个 SQL：

Explain plan for Select /*+ use_nl(b,a) leading(b) */ a.deptno, a.dname from dept a where exists (select 1 from emp b where a.deptno = b.deptno);

执行计划：

SQL> select * fromtable(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1511694371 ---------------------------------------------------------------------------------------- | Id | Operation |Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 3 | 105 | 7 (15)| 00:00:01 | | 1| NESTED LOOPS | | 3 | 105 | 7 (15)| 00:00:01 | | 2| NESTED LOOPS | | 3 | 105 | 7 (15)| 00:00:01 | | 3| SORT UNIQUE | | 14 | 182 | 3 (0)| 00:00:01 | | 4| TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 | |* 5| INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | 6| TABLE ACCESS BY INDEX ROWID|DEPT | 1 | 22 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified byoperation id): --------------------------------------------------- 5- access("A"."DEPTNO"="B"."DEPTNO")

等价改写

用 IN 来改写：

Explain plan for select a.deptno, a.dname from dept a where a.deptno in (select deptno from empb);

执行计划：

SQL> select * fromtable(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1505539579 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 3 | 105 | 3 (0)| 00:00:01 | | 1| NESTED LOOPSSEMI | | 3 | 105 | 3 (0)| 00:00:01 | | 2| TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 | |* 3| INDEX RANGE SCAN | IDEX_DEPTNO | 11 | 143 | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified byoperation id): --------------------------------------------------- 3- access("A"."DEPTNO"="DEPTNO")

改写成 inner join：

Explain plan for select a.deptno, a.dname from dept a,(select b.deptno from emp b group by b.deptno) c where a.deptno=c.deptno;

执行计划：

SQL> select * fromtable(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1766026621 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 14 | 658 | 4 (25)| 00:00:01 | | 1| HASH GROUP BY | | 14 | 658 | 4 (25)| 00:00:01 | | 2| NESTED LOOPS | | 14 | 658 | 3 (0)| 00:00:01 | | 3| TABLE ACCESS FULL| DEPT | 4 | 136 | 3 (0)| 00:00:01 | |* 4| INDEX RANGE SCAN | IDEX_DEPTNO| 4 | 52 | 0 (0)| 00:00:01 | ----------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified byoperation id): --------------------------------------------------- 4- access("A"."DEPTNO"="B"."DEPTNO")

Inner join 多了 group by 的操作，emp 的 depno 值不是唯一的，要保证逻辑上跟半连接的一致就需要把 emp 的连接进行去重操作，跟上面 emp 作为驱动表是一致的。

1.2 HASH JOIN SEMI

执行步骤：

优化器把主查询中的表当作驱动表，把 select 列和 join 列读入 pga 中，并把 join 列进行 hash 运算构建 hash table；
把另外一张表的连接列也进行 hash 运算，然后到 pga 中去探测 hash table ，如果关联上就返回驱动表的数据。

Hint /*+ use_hash(a,b) leading(a)*/

看下这个 SQL：

explainplan for Select a.* fromdept a whereexists (select 1 from emp b wherea.deptno = b.deptno);

执行计划：

SQL>select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Planhash value: 1754319153 --------------------------------------------------------------------------- |Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 129 | 6 (0)| 00:00:01 | |* 1 | HASH JOIN SEMI | | 3 | 129 | 6 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4| 120 | 3 (0)| 00:00:01 | | 3 | TABLEACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ PredicateInformation (identified by operation id): --------------------------------------------------- 1 -access("A"."DEPTNO"="B"."DEPTNO")

改变下驱动表：

explain plan for Select/*+ use_hash(a,b) leading(b)*/ a.* from dept a where exists (select 1 from emp b wherea.deptno = b.deptno);

执行计划：

SQL>select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Planhash value: 300394613 ---------------------------------------------------------------------------- |Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 129 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN | | 3 | 129 | 7 (15)| 00:00:01 | | 2 | SORT UNIQUE | | 14 | 182 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL | DEPT | 4| 120 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ PredicateInformation (identified by operation id): --------------------------------------------------- 1 - access("A"."DEPTNO"="B"."DEPTNO") Note ----- - dynamic sampling used for this statement(level=2)

Emp 连接列有重复值，需要进行去重操作，保证逻辑一致。

等价改写

用 IN 来改写：

explain plan for select a.* from dept a where a.deptno in (selectdeptno from emp b);

执行计划：

SQL>select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Planhash value: 1754319153 --------------------------------------------------------------------------- |Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 129 | 6 (0)| 00:00:01 | |* 1 | HASH JOIN SEMI | | 3 | 129 | 6 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 -access("A"."DEPTNO"="DEPTNO")

用内连接改写：

explain plan for select a.* from dept a ,(select b.deptno fromemp b group by b.deptno) c where a.deptno=c.deptno;

执行计划：

SQL>select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 3977374068 ----------------------------------------------------------------------------- |Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 602 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN | | 14 | 602 | 7 (15)| 00:00:01 | | 2 | TABLE ACCESS FULL | DEPT | 4 | 120 | 3 (0)| 00:00:01 | | 3 | VIEW | | 14 | 182 | 4 (25)| 00:00:01 | | 4 | HASH GROUP BY | | 14 | 182 | 4 (25)| 00:00:01 | | 5 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 | PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 -access("A"."DEPTNO"="C"."DEPTNO")

反连接的原理及等价改写

1. 什么是反连接

两表关联只返回主表的数据，并且只返回主表与子表没关联上的数据，这种连接就叫反连接。反连接一般就是指的 NOT IN 和 NOT EXISTS。

执行计划：

NESTED LOOP ANTI HASH JOIN ANTI

1.1 NESTED LOOP ANTI

NESTED LOOP ANTI 执行步骤：

优化器选择主查询中的表为驱动表；
选择子查询中的表为被驱动表；
把驱动表的每一行根据连接列去被驱动表中去查找匹配的行；
如果在被驱动表中没有匹配上，则返回驱动表中的没有匹配上的数据。

Hint /*+ use_nl(a,b) leading(b) */

在反连接中驱动表不会反生变化，因为反连接等价于外连接+子表连接条件 is null，使用嵌套循环进行关联的时候无法更改驱动表，驱动表会被固定为主表。

我们来看下下面这个执行计划：

explain plan for Select a.* from dept a where not exists (select 1 from emp b where a.deptno =b.deptno);

执行计划：

SQL>select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2795798678 -------------------------------------------------------------------------------- - |Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- - PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 172 | 3 (0)| 00:00:01 | | 1 | NESTED LOOPS ANTI | | 4 | 172 | 3 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IDX_DEPTNO | 1 | 13 | 0 (0)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- - Predicate Information (identified by operation id): --------------------------------------------------- 3 -access("A"."DEPTNO"="B"."DEPTNO")

使用/*+ use_nl(a,b) leading(b) */来更改驱动表看下：

explain plan for Select /*+ use_nl(a,b) leading(b) */ a.* from dept a where not exists (select 1 from emp b where a.deptno =b.deptno);

执行计划：

SQL>select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Planhash value: 2795798678 -------------------------------------------------------------------------------- - |Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- - PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 172 | 3 (0)| 00:00:01 | | 1 | NESTED LOOPS ANTI | | 4 | 172 | 3 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL|DEPT | 4 | 120 | 3 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IDX_DEPTNO | 1 | 13 | 0 (0)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- - Predicate Information (identified by operation id): --------------------------------------------------- 3 -access("A"."DEPTNO"="B"."DEPTNO")

等价改写

Not exists 改写为 not in，要注意子查询要排除 null 的情况，因为 not in 后面如果有 null 值整个查询都为空。

看下面这条 SQL：

explain plan for select a.* from dept a where a.deptno not in(select deptno from emp b where deptno is not null);

执行计划：

SQL>select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2795798678 -------------------------------------------------------------------------------- - |Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- - PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 172 | 3 (0)| 00:00:01 | | 1 | NESTED LOOPS ANTI | | 4 | 172 | 3 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IDX_DEPTNO | 1 | 13 | 0 (0)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- - PredicateInformation (identified by operation id): --------------------------------------------------- 3 -access("A"."DEPTNO"="DEPTNO") filter("DEPTNO" IS NOT NULL)

用外连接改写：

根据反连接定义，翻译为标准 SQL 写法就是外连接+子表连接条件 is null。外连接是只返回主表的数据，子表只返回关联的数据，加上子表条件 is null 就是只返回没有关联上的数据。

我们看下这条 SQL：

selecta.*,b.* from dept a left join emp b on a.deptno = b.deptno;

explainplan for select a.* from dept a left join emp b on a.deptno = b.deptno where b.deptno is null;

加上 b.deptno is null 就返回了没有关联上的数据。

执行计划：

SQL>select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Planhash value: 2795798678 -------------------------------------------------------------------------------- - |Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- - PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 172 | 3 (0)| 00:00:01 | | 1 | NESTED LOOPS ANTI | | 4 | 172 | 3 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IDX_DEPTNO | 1 | 13 | 0 (0)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- - PredicateInformation (identified by operation id): --------------------------------------------------- 3 -access("A"."DEPTNO"="B"."DEPTNO")

1.2 HASH JOIN ANTI

执行步骤：

优化器把主查询中的表当作驱动表，把 select 列和 join 列读入 pga 中，并把 join 列进行 hash 运算构建 hash table；
把另外一张表的连接列也进行 hash 运算，然后到 pga 中去探测 hash table ，如果没有关联上就返回驱动表的数据。

两表关联如果是外连接，要改变 hash 连接的驱动表必须使用 swap_join_inputs。

看下面这个 SQL：

explainplan for Select a.* fromdept a wherenot exists (select 1 from emp b wherea.deptno =b.deptno);

执行计划：

PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Planhash value: 474461924 --------------------------------------------------------------------------- |Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 172 | 6 (0)| 00:00:01 | |* 1 | HASH JOIN ANTI | | 4 | 172 | 6 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ PredicateInformation (identified by operation id): --------------------------------------------------- 1 -access("A"."DEPTNO"="B"."DEPTNO")

加 hint，更改驱动表

explainplan for Select /*+ use_hash(dept,emp@c) swap_join_inputs(emp@c)*/ * fromdept wherenot exists (select /*+ qb_name(c)*/ 1from emp where emp.deptno = dept.deptno);

执行计划：

SQL>select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hashvalue: 152508289 ----------------------------------------------------------------------------- |Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 172 | 6 (0)| 00:00:01 | |* 1 | HASH JOIN RIGHT ANTI| | 4 | 172 | 6 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL | EMP | 14| 182 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL | DEPT | 4 | 120 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ PredicateInformation (identified by operation id): --------------------------------------------------- 1 -access("EMP"."DEPTNO"="DEPT"."DEPTNO")

等价改写

Not in 改写：

explain plan for select a.* from dept a where a.deptno not in(select deptno from emp b where deptno is not null);

执行计划：

SQL>select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 474461924 --------------------------------------------------------------------------- |Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 172 | 6 (0)| 00:00:01 | |* 1 | HASH JOIN ANTI | | 4 | 172 | 6 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4| 120 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 -access("A"."DEPTNO"="DEPTNO") 3 - filter("DEPTNO" IS NOT NULL)

改为外连接：

explain plan for select a.* from dept a left join emp b on a.deptno = b.deptno where b.deptno is null;

执行计划：

SQL> select * fromtable(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 474461924 --------------------------------------------------------------------------- | Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 4 | 172 | 6 (0)| 00:00:01 | |* 1| HASH JOIN ANTI | | 4 | 172 | 6 (0)| 00:00:01 | | 2| TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 | | 3| TABLE ACCESS FULL| EMP | 14| 182 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified byoperation id): --------------------------------------------------- 1- access("A"."DEPTNO"="B"."DEPTNO")

总结

通过半连接和反连接的学习，我们可以知道半连接一般是出现在子查询有 IN 或者 EXISTS 的情况，并且只返回主表连接列关联上的数据，如果连接列是唯一值的话可以直接转化为内连接，反连接一般是出现在子查询有 NOT IN 或者 NOT EXISTS 的情况，并且只返回主表连接列没有关联上的数据，NEST LOOP ANTI 不能改驱动表。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2018-03-26，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

本文分享自数据和云微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

sql

登录后参与评论

0 条评论

热度