本文系转载,如果有侵犯您知识产权,烦请及时通知本人,本人将即刻停止侵权行为;
转载网址:http://longmans1985.blog.163.com/blog/static/70605475201121711229521/
http://blog.itpub.net/post/901/12680
使用exists、not eixsts不能用到semi-join、anti-join的一些情况讨论
http://www.itpub.net/thread-438650-1-1.html:
简单介绍join,outer-join,semi-join,anti-join的区别
http://www.itpub.net/thread-438650-1-1.html
Speeding Up Queries with Semi-Joins and Anti-Joins: How Oracle Evaluates EXISTS, NOT
EXISTS, IN, and NOT IN:http://www.dbspecialists.com/files/presentations/semijoins.html
Joins In Oracle:http://www.tutorialall.com/oracle/joins/index.php
---begin
表连接的方式如join,semi-join,outer-join,anti-join;
表连接的实现方式如nested loop,merge,hash.
本文简单的介绍表连接的方式join,semi-join,outer-join,anti-join和适用情景。
假设2个数据源(row source):Emp(id pk,ename,deptno) Dept(deptno pk,dname)
如下是join
select ename,dname from emp,dept where emp.deptno=dname.deptno;
2个数据源键值一一比较,返回相互匹配的记录集
for example: nested loop join
1 for x in ( select * from emp ) 2 loop 3 for y in ( select * from dept) 4 loop 5 if ( x.deptno == y.deptno ) 6 OutPut_Record(x.ename,y.dname) 7 End if 8 end loop 9 end loop
outer-join
select ename,dname from emp,dept where emp.deptno= dept.deptno(+);
select ename,dname from emp,dept where emp.deptno(+)=dept.deptno;
2个数据源键值一一比较,返回相互匹配的;但如果在另外一个row source没有找到匹配的也返回记录
for example: nested loop outer-join
1 for x in ( select * from emp ) 2 loop 3 find_flag=false; 4 for y in ( select * from dept) 5 loop 6 if ( x.deptno == y.deptno ) 7 OutPut_Record(x.ename,y.dname) 8 Find_flag=true 9 End if 10 end loop 11 if ( find_flag == false ) 12 OutPut_Record(x.ename,null) 13 End if 14 end loop
semi-join
select dname from dept where exists( select null from emp where emp.deptno=dept.deptno)
多在子查询exists中使用,对外部row source的每个键值,查找到内部row source匹配的第一个键值后就返回,如果
找到就不用再查找内部row source其他的键值了。(对于这个结论有一定的争论,请参考,上面的网址连接)
for example: nested loop semi-join
1 for x in ( select * from dept ) 2 loop 3 for y in ( select * from emp) 4 loop 5 if ( x.deptno == y.deptno ) 6 OutPut_Record(x.dname) 7 Break; 8 End if 9 end loop 10 end loop
anti-join
select ename,deptno from emp,dept where emp.deptno!=dept.deptno
多用于!= not in 等查询;如果找到满足条件(!= not in)的不返回,不满足条件(!= not in)的返回。和join相反。
for example: nested loop anti-join
1 for x in ( select * from emp ) 2 loop 3 for y in ( select * from dept) 4 loop 5 if ( x.deptno != y.deptno ) 6 OutPut_Record(x.dname,y.deptno) 7 End if 8 end loop 9 end loop