Joins in Oracle

Joins

A join is used to combine rows from multiple tables.
A join is performed whenever two or more tables is listed in the FROM clause of an SQL statement.

Inner Join (simple join)/Equi Join:
----------------------------------
It is the most common type of join. Inner joins return all rows from multiple tables
where the join condition is met.

For example, CONTRACT AND PRODUCT TWO TABLES

sql> SELECT * FROM CONTRACT C,PRODUCT P WHERE C.PRODUCT_CODE=P.PRODUCT_CODE

sql> SELECT * FROM CONTRACT C INNER JOIN PRODUCT P ON(C.PRODUCT_CODE=P.PRODUCT_CODE) WHERE SUM_ASSURED>50000;

This SQL statement would return all rows from the CONTRACT and PRODUCT tables
where there is a matching PRODUCT_CODE value in both the CONTRACT and PRODUCT tables.

Cross Join:
-----------
Join without filter conditions.
A Cross Join is the Cartesian product or the result of all possible combinations of the rows
 from each of the tables involved in the join operation.
This occurs when, no specific Join conditions (filters) are specified.

For example, CONTRACT AND PRODUCT TWO TABLES
sql> SELECT * FROM CONTRACT C,PRODUCT P ---NO CONDITION
This SQL statement would return the number of rows in product table multiply with number of rows in contract table.
contract table having 10 rows and product table 5 rows then output becomes 10*5.

Natural Join: (ANSI Joins):
--------------------------
These are ANSI Joins which are used for portability. You can use this in almost all Standard Databases
 like Oracle, Microsoft SQL Server etc.
SQL> SELECT DNAME, ENAME, MGR FROM DEPARTMENTS NATURAL JOIN EMPLOYEES;

NOte: Both tables should have primary key-referential key relationship.

Self Join:----------
  Sometimes there are scenarios where we might need to do Join operations within the same table.
 Such joins which refers to the same, single table are known as a Self Joins.
For Eg: If you need to get all employees and their managers, then you could do a self join.
          
SQl>SELECT  E1.ENAME||' reports to '||E2.ENAME FROM EMP E1,EMP E2 WHERE E1.MGR = E2.EMPNO;

Outer Join:
------------
They are of 2 types:
a) Left Outer Join
b) Right Outer Join

Left Outer Join:----------------
Returns all records from table A and only those matching with the join operation from Table B
For eg:
SQl> select * from emp left outer join dept on(emp.deptno-dept,deptno);

SQl> SELECT * FROM  EMP, DEPT WHERE EMP.DEPTNO  = DEPT.DEPTNO (+);
Returns all records from EMP and only those records from DEPT
which matches the condition EMP.DEPTNO  = DEPT.DEPTNO

SQL> select *  from contract left outer join product using (author_key)
    order by product_code;

Right Outer Join:----------------
Returns all records from table B and only those matching with the join operation from Table A
(just the reverse of left outer join)
For eg:

SQl> SELECT * FROM    EMP, DEPT WHERE EMP.DEPTNO (+)  = DEPT.DEPTNO ;
SQl> select * from emp Right outer join dept on(emp.deptno-dept,deptno);

Returns all records from DEPT and only those records from EMP
which matches the condition EMP.DEPTNO  = DEPT.DEPTNO


Ur's
AmarAlam