Semi-Join” Defined

A semi-join between two tables returns rows from the first table where one or more matches are found in the second table.

The difference between a semi-join and a conventional join is that rows in the first table will be returned at most once. Even if the second table contains two matches for a row in the first table, only one copy of the row will be returned.

Semi-joins are written using EXISTS or IN.

Example of a Query written with a semi-join: The query below will list departments with at least one employee.

SELECT D.deptno, D.dname
FROM dept D
WHERE EXISTS
(SELECT 1
FROM emp E
WHERE E.deptno = D.deptno)
ORDER BY D.deptno;

No department will appear more than once as Oracle will stop processing each department as soon as the first employee in that department is found.