简单介绍join,outer-join,semi-join,anti-join的区别

本文简单的介绍表连接的方式join,semi-join,outer-join,anti-join和适用情景。

假设２个数据源(row source)．

Emp(id pk,ename,deptno)           Dept(deptno pk,dname)

如下是join

 select ename,dname from emp,dept where emp.deptno=dname.deptno;

２个数据源键值一一比较，返回相互匹配的记录集

for example: nested loop join

for x in ( select * from emp )    loop        for y in ( select * from dept)            loop                if ( x.deptno == y.deptno )                    OutPut_Record(x.ename,y.dname)                End if            end loop    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;

２个数据源键值一一比较，返回相互匹配的；但如果在另外一个row source没有找到匹配的也返回记录

for example: nested loop outer-join

for x in ( select * from emp )    loop        find_flag=false;        for y in ( select * from dept)            loop                if ( x.deptno == y.deptno )                    OutPut_Record(x.ename,y.dname)                    Find_flag=true                End if            end loop        if ( find_flag == false )            OutPut_Record(x.ename,null)        End if    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

for x in ( select * from dept )    loop        for y in ( select * from emp)            loop                if ( x.deptno == y.deptno )                    OutPut_Record(x.dname)                    Break;                End if            end loop    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

for x in ( select * from emp )    loop        for y in ( select * from dept)            loop                if ( x.deptno != y.deptno )                    OutPut_Record(x.dname,y.deptno)                End if            end loop    end loop

转载自：http://biancheng.dnbcw.info/oracle/313930.html