About semi-join in Oracle

About semi-join in Oracle

Table join is particularly important in SQL statements. External connections, internal connections, semi-connections, anti-connections, and other connections, there are still a lot of details in a simple connection. It is also important for SQL optimization.

The SQL statements in the following form belong to semi-join, and the in clause is used. The implementation of exists is also a semi-join.

-- In semi-join
SQL> select dname from dept where deptno in (select deptno from emp );

DNAME
--------------
RESEARCH
SALES
ACCOUNTING

-- Exists semi-join

SQL> select dname from dept where exists (select null from emp where emp. deptno = dept. deptno)
2/

DNAME
--------------
RESEARCH
SALES
ACCOUNTING

We may not agree with the above two connections. We think that the result of directly placing the tables to be used in the from clause is the same, and the answer is not certain.
For example, in the following form, there may be a lot more output results. About 14 records, but three records are output through semi-join.
SQL> select dept. dname from dept, emp empwhere dept. deptno = emp. deptno;
DNAME
--------------
RESEARCH
SALES
SALES
RESEARCH
SALES
SALES
ACCOUNTING
RESEARCH
ACCOUNTING
SALES
RESEARCH
SALES
RESEARCH
ACCOUNTING
14 rows selected.

Therefore, if you want to get the same output result, you still need distinct + inner join.

SQL> select distinct dept. dname from dept, emp where dept. deptno = emp. deptno;

DNAME
--------------
ACCOUNTING
RESEARCH
SALES

The following methods can be used to replace semi-connections:
-- Use a set

Select dept. dname from dept,
(Select deptno from dept
Intersect
Select deptno from emp) B
Where dept. deptno = B. deptno;

DNAME
--------------
ACCOUNTING
RESEARCH
SALES

-- Use any

SQL> select dept. dname from dept where deptno = any (select deptno from emp );

DNAME
--------------
RESEARCH
SALES
ACCOUNTING

-- Use distinct and inner connection

SQL> select distinct emp. deptno from dept, emp
Where dept. deptno = emp. deptno;
DEPTNO
----------
30
20
10
SQL> select distinct dept. deptno from dept, emp
Where dept. deptno = emp. deptno;
DEPTNO
----------
30
20
10

In general, some implementations of semi-join may be seen in the most intuitive way through the execution plan. Semi-join is enabled and semi is displayed in the execution plan.

You can also manually specify that a semi-join is not required. Use Hint no_semijoin

Oracle semi-Join learning Summary