Join Types (inner join、outer join、semijoins、antijoins、cartesian joins)

quoted from:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/joins.html#GUID-8E7760A6-48D6-4794-BF2F-290349C019B9

---

A join type is determined by the type of join condition.

This section contains the following topics:

• Inner Joins
  An inner join (sometimes called a simple join) is a join that returns only rows that satisfy the join condition. Inner joins are either equijoins or nonequijoins.
• Outer Joins
  An outer join returns all rows that satisfy the join condition and also rows from one table for which no rows from the other table satisfy the condition. Thus, the result set of an outer join is the superset of an inner join.
• Semijoins
  A semijoin is a join between two data sets that returns a row from the first set when a matching row exists in the subquery data set.
• Antijoins
  An antijoin is a join between two data sets that returns a row from the first set when a matching row does not exist in the subquery data set.
• Cartesian Joins
  The database uses a Cartesian join when one or more of the tables does not have any join conditions to any other tables in the statement.

---

 

An inner join (sometimes called a simple join) is a join that returns only rows that satisfy the join condition. Inner joins are either equijoins or nonequijoins.

This section contains the following topics:

• Equijoins
  An equijoin is an inner join whose join condition contains an equality operator.
• Nonequijoins
  A nonequijoin is an inner join whose join condition contains an operator that is not an equality operator.
• Band Joins
  A band join is a special type of nonequijoin in which key values in one data set must fall within the specified range (“band”) of the second data set. The same table can serve as both the first and second data sets.

---

This section contains the following topics:

• Nested Loops Outer Joins
  The database uses this operation to loop through an outer join between two tables. The outer join returns the outer (preserved) table rows, even when no corresponding rows are in the inner (optional) table.
• Hash Join Outer Joins
  The optimizer uses hash joins for processing an outer join when either the data volume is large enough to make a hash join efficient, or it is impossible to drive from the outer table to the inner table.
• Sort Merge Outer Joins
  When an outer join cannot drive from the outer (preserved) table to the inner (optional) table, it cannot use a hash join or nested loops joins.
• Full Outer Joins
  A full outer join is a combination of the left and right outer joins.
• Multiple Tables on the Left of an Outer Join
  In Oracle Database 12c, multiple tables may exist on the left of an outer-joined table. This enhancement enables Oracle Database to merge a view that contains multiple tables and appears on the left of outer join.