There are mainly 3 types of Joins in Oracle - Cartesian Join or Cross Join, Inner Join and Outer Join
Cartesian Join: This join returns the cartesian product of two tables i.e. match all the rows of one table to every rows of another table
Inner Join: when two tables are joined to retrieve all the matched rows in two tables
Outer Join: There are different flavors of Outer Joins: Full Outer Join, Left Outer Join, Right Outer Join. They're described with exmple illustration below:
Full outer join
Takes all matched rows from both table and then all the unmatched rows from left as well as right table
select one.id as one_id, one.name as one_name , two.id as two_id, two.address as two_address
from one full outer join two on one.id = two.id
Left outer join
Takes all the matches as well as the unmatched rows from left table (i.e. table one)
Deprecated syntax:
select one.id as one_id, one.name as one_name , two.id as two_id, two.address as two_address
from one, two
where one.id = two.id(+)
Suggested syntax:
from table1 left outer join table2
on table1.col = table2.col
Right outer join
Takes all the rows that have match as well as the all the left over rows from the right table (i.e. table two)
Deprecated syntax
select one.id as one_id, one.name as one_name , two.id as two_id, two.address as two_address
from one, two
where one.id(+) = two.id
Suggested syntax
select one.id as one_id, one.name as one_name , two.id as two_id, two.address as two_address
from one right outer join two on one.id = two.id
There is another flavors of Join which is known as Self Join where both the tables are actually the same table but used with alias. All the 3 kinds of Joins mentioned above can be used for the self join
Resources
http://www.praetoriate.com/oracle_tips_outer_joins.htm - Donald K. Burleson on Joins. He writes very straight & simple.
http://www.sqlteam.com/article/additional-criteria-in-the-join-clause - Filtering the join
Cartesian Join: This join returns the cartesian product of two tables i.e. match all the rows of one table to every rows of another table
Inner Join: when two tables are joined to retrieve all the matched rows in two tables
Outer Join: There are different flavors of Outer Joins: Full Outer Join, Left Outer Join, Right Outer Join. They're described with exmple illustration below:
Full outer join
Takes all matched rows from both table and then all the unmatched rows from left as well as right table
select one.id as one_id, one.name as one_name , two.id as two_id, two.address as two_address
from one full outer join two on one.id = two.id
Left outer join
Takes all the matches as well as the unmatched rows from left table (i.e. table one)
Deprecated syntax:
select one.id as one_id, one.name as one_name , two.id as two_id, two.address as two_address
from one, two
where one.id = two.id(+)
Suggested syntax:
from table1 left outer join table2
on table1.col = table2.col
Right outer join
Takes all the rows that have match as well as the all the left over rows from the right table (i.e. table two)
Deprecated syntax
select one.id as one_id, one.name as one_name , two.id as two_id, two.address as two_address
from one, two
where one.id(+) = two.id
Suggested syntax
select one.id as one_id, one.name as one_name , two.id as two_id, two.address as two_address
from one right outer join two on one.id = two.id
There is another flavors of Join which is known as Self Join where both the tables are actually the same table but used with alias. All the 3 kinds of Joins mentioned above can be used for the self join
Resources
http://www.praetoriate.com/oracle_tips_outer_joins.htm - Donald K. Burleson on Joins. He writes very straight & simple.
http://www.sqlteam.com/article/additional-criteria-in-the-join-clause - Filtering the join
