Oracle SQL Join Methods

                              Oracle SQL Join Methods 

Oracle SQL Join Methods

An SQL JOIN clause combines records from two tables in a database, resulting in a new, temporary table, sometimes called a "joined table". You may also think of JOIN as an SQL operation that relates tables by means of values common between them. SQL specifies four types of JOIN: INNER, OUTER, LEFT, and RIGHT. In special cases, a table (base table, view, or joined table) can JOIN to itself in a self-join.
Cartesian Product
A cross join, cartesian join or product provides the foundation upon which all types of inner joins operate. A cross join returns the cartesian product of the set of records from two joined tables.
If you have two tables A and B, then a cross join will be = A x B. For example if table A has 8 rows and table B has 5 rows then the result set would return 40 rows ( 8 rows x 5 rows= 40 rows).
This type of join can be quite dangerous on large tables, as it will have a impact on server performance.
Natural Join
When we join two tables with a natural join, Oracle joins all those columns from the first table to any column in the second table with the same name. In natural joins, you do not explicitly name columns to use to perform the join, Oracle will perform this for us.

   select * from employee natural join dept;
Inner Join
Inner joins use join conditions using the from clause just like natural joins, but you must specify the columns from each table you join on. When you perform a inner join only the rows that satisfy the join condition are returned (the gray part).

   -- explicit inner join
   select * from employee inner join dept on employee.dept_id = dept.dept_id;

   -- implicit inner join
   select * from employee, dept where employee.dept_id = dept.dept_id;

Right Outer Join
A right outer join will return all the rows from the table on the right of the condition statement instead of the left, in the example below the rows from all the dept table will be returned plus any matching ones from the left table.
   select * from employee right outer join dept on employee.dept_id = dept.dept_id;

Left Outer Join
A left outer join will return all the rows from the table on the left of the condition statement instead of the right, in the example below the rows from all the employee table will be returned plus any matching ones from the right table.
   select * from employee left outer join dept on employee.dept_id = dept.dept_id;

Full Outer Join
A full outer join will return all rows from both tables, it is basically a union of a right outer join and a left outer join.
   select * from employee full outer join dept on employee.dept_id = dept.dept_id;

   select * from employee left outer join dept on employee.dept_id = dept.dept_id
   union
   select * from employee right outer join dept on employee.dept_id = dept.dept_id;

Self Join
In some tables you create, you may have what is known as a self-referencing foreign key. This means one column is the foreign key to the primary key of the same table. An example of this is the manager_id column which references an emp_id column. when writing a a self join you specify the source table twice.

   select e1.last_name "Employee", e2.last_name "Manager" from employee e1 left outer join employee e2 on e1.manager_id =    e2.employee_id;