A semijoin returns rows that match an EXISTS subquery without duplicating rows from the left side of the predicate when multiple rows on the right side satisfy the criteria of the subquery.
Semijoin and antijoin transformation cannot be done if the subquery is on an OR branch of the WHERE clause.
A semi-join is useful when a subquery is introduced using the EXISTS or IN operator. A semi-join allows to convert the EXISTS subquery into a join. This option is called a semi-join because, unlike a regular join, a single row only is returned from the driving table even if multiple matching rows exist in the join table.
To encourage Oracle to perform a semi-join, add the HASH SEMI-JOIN or the MERGE SEMI-JOIN hint to the subquery.
| table1 (inner set) | table2 (outer set) |
|
|---|---|---|
| Column ID | Column ID | Column ID_2 |
| A | A | |
| B | B | A |
| D | C | C |
gerardnico@orcl>select table1.id "ID_table1"
2 from table1
3 where EXISTS ( select * from table2 where table1.Id = table2.Id );
ID_table1
----------
A
B
A and B are present in the table1 and in the table2.