Differences between SQL join, outer-join, semi-join, and 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 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.

The code is as follows:	Copy code
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.

The code is as follows:	Copy code
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 each key value of the external row source, find the first key value that matches the internal row source, and then return, if it is found, you do not need to find other key values of the internal row source.
For example: nested loop semi-join

 

The code is as follows:	Copy code
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

The code is as follows:	Copy code
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