Semi-join anti-join

Table join methods include join, semi-join, outer-join, and anti-join;

Table join implementation methods such as nested loop, merge, and hash.

This article briefly introduces table join, semi-join, outer-join, anti-join, and applicable scenarios.

Assume that two data sources (row
Source ).

EMP (id pk, ename, deptno) dept (deptno PK, dname)

Join

Select ename, dname from EMP, DEPT where EMP. deptno = dname. deptno;

Two data source key values are compared one by one, and a matching record set is returned.

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;

The key values of the two data sources are compared one by one, and matched results are returned. However, if no match is found in the other row source, a record is returned.

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)

Mostly used in the subquery exists, for external row
Each key value of source,Find internalReturns the first key value matched by row source.If it is found, you do not need to find the internal row.
Other source key values.

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

Mostly used! = Not in and other queries; if the conditions are found (! = Not
In) does not return, does not meet the condition (! = Not in. Is opposite to 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

More information get document