原博客地址:http://space.itpub.net/15480802/viewspace-703260
create table dept (deptno number(5), dname varchar2(20));
create table emp (deptno number(5), name varchar2(20));
insert into dept values(1,'IT');
insert into dept values(2,'HR');
insert into dept values(3,'Marketplace');
insert into emp values(1,'justin1');
insert into emp values(1,'justin2');
insert into emp values(2,'justin3');
commit;
Semi-join
通常出现在使用了exists或in的sql中,所谓semi-join即在两表关联时,当第二个表中存在一个或多个匹配记录时,返回第一个表的记录;
与普通join的区别在于semi-join时,第一个表里的记录最多只返回一次;
例1
现在要查询出职员不为空的部门
普通的表连接会返回重复值
SQL> select d.deptno,d.dname from dept d, emp e where e.deptno = d.deptno;
DEPTNO DNAME
------ --------------------
1 IT
1 IT
2 HR
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 114 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 3 | 114 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 3 | 75 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 3 | 39 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
而使用semi-join时候,不会返回重复记录
SQL> select d.deptno,d.dname from dept d where d.deptno in (select e.deptno from emp e);
DEPTNO DNAME
------ --------------------
1 IT
2 HR
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 76 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 2 | 76 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 3 | 75 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 3 | 39 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Anti-join
而anti-join则与semi-join相反,即当在第二张表没有发现匹配记录时,才会返回第一张表里的记录;
当使用not exists/not in的时候会用到,两者在处理null值的时候会有所区别
何时选择anti-join
1 使用not in且相应列有not null约束
2 not exists,不保证每次都用到anti-join
当无法选择anti-join时,oracle常会采用filter替代
例2
查询职员为空的部门
普通sql
SQL> select d.deptno,d.dname from dept d
2 minus
3 select d.deptno,d.dname from dept d where d.deptno in (select e.deptno from emp e);
DEPTNO DNAME
------ --------------------
3 Marketplace
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 189 | 12 (75)| 00:00:01 |
| 1 | MINUS | | | | | |
| 2 | SORT UNIQUE | | 3 | 75 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT | 3 | 75 | 3 (0)| 00:00:01 |
| 4 | SORT UNIQUE | | 3 | 114 | 8 (25)| 00:00:01 |
|* 5 | HASH JOIN | | 3 | 114 | 7 (15)| 00:00:01 |
| 6 | TABLE ACCESS FULL| EMP | 3 | 39 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL| DEPT | 3 | 75 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
使用anti join
SQL> select d.deptno,d.dname from dept d
2 where d.deptno not in (select deptno from emp);
DEPTNO DNAME
------ --------------------
3 Marketplace
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 114 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN ANTI NA | | 3 | 114 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 3 | 75 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 3 | 39 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
注:倘若subquery返回一条null值,则整个not in都变为false,即不返回任何值
If the subquery of a NOT IN clause returns at least one row with a null value, the entire NOT IN clause evaluates to false for all rows
SQL> select d.deptno,d.dname from dept d where d.deptno not in (select deptno from emp);
DEPTNO DNAME
---------- --------------------
3 Marketplace
SQL> insert into emp values(null,'NULL');
1 row created.
SQL> commit;
Commit complete.
SQL> select d.deptno,d.dname from dept d where d.deptno not in (select deptno from emp);
no rows selected
而换成not exists时候,hash join anti na变成了hash join anti
select d.deptno,d.dname from dept d where not exists (select deptno from emp where emp.deptno = d.deptno);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 114 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 3 | 114 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 3 | 75 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 3 | 39 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Not exists与not in的区别在于not exists不受null值影响
注意
Oracle在解析sql时候会尽可能的把子查询转换为表连接
Oracle在以下情况不会使用semi-join
1、 使用distinct或者union
2、 Exists/in子查询中使用了or
Hash_sj/merge_sj/nl_sj都是关于semi-join的一些hint
相关推荐
Semi-globally/globally stable adaptive NN backstepping control for uncertain MIMO systems with tracking accuracy known a priori
北大POJ3292-Semi-prime H-numbers 解题报告+AC代码
is denoted as Semi-Global Weighed Least Squares (SG- WLS). Instead of solving a large linear system, we pro- pose to iteratively solve a sequence of subsystems which are one-dimensional WLS models. ...
半柔性印刷电路板(Semi-Flex PCB),是在标准的硬板加工过程中结合入控深铣削加工或刚挠板加 工技术(铣开盖或开通窗等)获得的一种用于静态弯折领域的 PCB,常采用非对称的叠层结构,只适用 于受静态应力的安装...
Graph-based methods have been demonstrated as one of the most effective approaches for semi-supervised learning,astheycanexploittheconnectivitypatternsbetweenlabeled and unlabeled data samples to ...
In order to adapt to this phenomenon, several semi-supervised learning (SSL) algorithms, which learn from labeled as well as unlabeled data, have been developed. In a separate line of work, ...
#资源达人分享计划#
半非负矩阵分解算法(Semi-NMF)的源代码,算法特别简练。
Semi-Supervised Learning
FLOWPRINT-Semi-Supervised Mobile-App.pdf
Semi-supervised learning with gaussian field and harmonic function. 一种利用高斯域和和谐函数的半监督分类方法.
semi-supervised CCA
Semi-supervised learning
点状半监督流形正则化分类学习,汪云云,沈雅婷,流形正则化(MR)是经典半监督分类学习方法,同时利用有标记和无标记样本进行学习。依据流形假设,约束流形结构图上相似样本具有相��
SEMI-S2半导体制程设备安全准则
Deep Learning via Semi-Supervised Embedding.pdf )
关于Combining active learning and semi-supervised learning to construct SVM classifier的文章
半监督分类算法,其中有一些例子可供大家使用
【keywords】semi-supervised learning, transductive learning, self-training,Gaussianmixturemodel, expectation maximization (EM), cluster-then-label, co-training, multiview learning, mincut,harmonic ...
第4节介绍semi-structured环境的路径规划。 free space的方法分为2部分,第1部分是采用启发式搜索方法获得一条满足车辆运动学约束的可行驶轨迹,比如Hybrid A*方法,通常结果不是最优、但接近最优。第2部分是在此...