SQL - Semijoins

Data System Architecture

About

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.

Semijoins and Oracle

To encourage Oracle to perform a semi-join, add the HASH SEMI-JOIN or the MERGE SEMI-JOIN hint to the subquery.

Example

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.





Discover More
Card Puncher Data Processing
Oracle Database - Join (Operation|Method|Mechanism|Algorithm)

Joins are data operation / method / algorithm in order to match data from more than one table. They all require sorting and then matching aspects of a dataset. There are only three join mechanisms used...
Data System Architecture
SQL - Joins (Home)

in SQL. A join is a SQL clause statement which define the set operation such as: intersection, union, that the database must perform between two or more relation (table, view, query, ...). The...



Share this page:
Follow us:
Task Runner