Oracle外连接等价于反连接的情况
Oracle外连接等价于反连接的情况
在Oracle外连接等价于内连接的情况中提到了外连接中被驱动表的字段作为谓词条件出现在where中，并且不为is null的话，等价于内连接。
当外连接中被驱动表的连接列作为谓词条件出现在where中，且是做is null过滤，若select出现的列都属于驱动表的时候，这种情况下实际等价于反连接。
如下：
3条sql均等价，执行计划均相同，可以互相改写，但是需要特别注意改写为not in的SQL中无论是驱动表还是被驱动表需要加上连接列 is not null的限制，因为前2条t.col=tt.col已经排除了null值。
第一条sql执行计划中id=1出席那了ANTI关键字表示执行路径本质是反连接。
select t.* from t left join tt on t.col = tt.col where tt.col is null; select t.* from t where not exists (select 1 from tt where t.col=tt.col); select t.* from t where t.col not in (select tt.col from tt where tt.col is not null) and t.col is not null; Plan hash value: 3667628246 Plan hash value: 3667628246 Plan hash value: 3667628246 ------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.02 | 52 | | | | | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 52 | | | | | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 52 | | | | |* 1 | HASH JOIN RIGHT ANTI| | 1 | 1000K| 1 |00:00:00.02 | 52 | 2616K| 2616K| 3107K (0)| |* 1 | HASH JOIN RIGHT ANTI| | 1 | 1000K| 1 |00:00:00.01 | 52 | 2616K| 2616K| 902K (0)| |* 1 | HASH JOIN RIGHT ANTI| | 1 | 1000K| 1 |00:00:00.01 | 52 | 2616K| 2616K| 3112K (0)| | 2 | TABLE ACCESS FULL | TT | 1 | 1000K| 2 |00:00:00.01 | 6 | | | | | 2 | TABLE ACCESS FULL | TT | 1 | 1000K| 2 |00:00:00.01 | 6 | | | | | 2 | TABLE ACCESS FULL | TT | 1 | 1000K| 2 |00:00:00.01 | 6 | | | | | 3 | TABLE ACCESS FULL | T | 1 | 1000K| 24578 |00:00:00.01 | 46 | | | | | 3 | TABLE ACCESS FULL | T | 1 | 1000K| 24578 |00:00:00.01 | 46 | | | | | 3 | TABLE ACCESS FULL | T | 1 | 1000K| 24578 |00:00:00.01 | 46 | | | | ------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): Predicate Information (identified by operation id): Predicate Information (identified by operation id): --------------------------------------------------- --------------------------------------------------- --------------------------------------------------- 1 - access("T"."COL"="TT"."COL") 1 - access("T"."COL"="TT"."COL") 1 - access("T"."COL"="TT"."COL")
