Oracle Joins

Joins

A join is a query that combines rows from two or more tables, views, or materialized views.

Most join queries contain at least one join condition, either in the FROM clause or in the WHERE clause.

Note:You cannot specify LOB columns in the WHERE clause if the WHERE clause contains the join condition

•  Equijoins 
•  Self Joins 
•  Cartesian Products 
•  Inner Joins 
•  Outer Joins 
•  Antijoins 
•  Semijoins

Equijoins is a join with a join condition containing an equality operator

Self join is a join of a table to itself

If two tables in a join query have no join condition, then Oracle Database returns their Cartesian product. Oracle combines each row of one table with each row of the other. A Cartesian product always generates many rows and is rarely useful

Inner join is a join of two or more tables that returns only those rows that satisfy the join condition.

Outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

o   Left Outer Join : outer join of tables A and B and returns all rows from A

o   Right Outer Join : outer join of tables A and B and returns all rows from B

o   Full Outer Join : returns all rows from A and B, extended with nulls if they do not satisfy the join condition

Anti-join returns rows from the left side of the predicate for which there are no corresponding rows on the right side of the predicate. It returns rows that fail to match (NOT IN) the sub-query on the right side.

Semi-join returns rows that match an EXISTS sub-query without duplicating rows from the left side of the predicate when multiple rows on the right side satisfy the criteria of the subquery