Types Of Joins

Cartesiion Product
When a join condition is invalid or omitted completely , the result is a Cartesian product first tables are joined to all rows in second table.




Numberof rows in Ist table * number of rows in IInd table = Cartesion Product


Oracle Proprietary Joins                                                     

1. EquiJoin
2. Non-Equijoin
3. Outer Join
4. Self Join

SQL:199

1. Cross Joins
2. Natural Joins
3. Using Clause
4. Full or two sided Outer Joins
5. Arbitrary Joins Conditions For Outer Joins .

To join n tables together we need a mininum of (N-1) join contions

Equi Join

Also known as simple Joins,Inner Joins This type of joins involved primary and foreign key complements.
Example:
SELECT e.last_name,d.department_name,l.city
FROM employees e,department d,location l
WHERE e.departmnt_id=d.department and d.location_id=l.location_id.