Frequently Asked Questions about Joining

Q: How the Optimizer Executes Join Statements ?

Q: What do you know about Inner Join ? 

Q: What do you know about Outer Join ?

Q: What is the difference between the "JOIN" and "INNER JOIN" ?

Q: What is the difference between the "Left Join" and "Left outer Join" ?

Q: What is the difference between the right and left outer joins ?

JOIN

The join clause combines the output from two row sources, such as tables or views, and returns one row source. The returned row source is the Data Set.

Whenever multiple tables exist in the FROM clause, Oracle Database performs a join.

The optimizer must make the decision depending on the following interrelated terms in oracle:

1. Join Type           
    - Inner (EquiJoin, Non-EquiJoin),  
    - Outer (Left, Right, Full),
    - Semi ,
    - Anti
    - Self Join   
    - Cross Join

2. Join Condition  - ON
3. Join Methods    - Nested loop, Sort merge, Hash joins
4. Join Order         - Table order   
5. Access path      - Full table scan, Index scan


More