Monday, January 28, 2013

JOINs in ORACLE


WHAT IS JOIN: 
JOIN is to combine two tables to be one in relational database.

CONDITION CLAUSE:
1. using clause:
  using (department_ID)
  using ( department_ID, employee_ID)
the column name must be same in the parenthesis of using clause

2. on clause:
  on ( a.department_ID = b.dpartment_ID)
  on (a.dpartment_ID = b.department_ID and a.employee_ID = b.employee_ID)
  on (a.dpartment_ID = b.department_ID and a.employee_ID > b.employee_ID)
  on (a.dpartment_ID = b.department_ID and a.employee_ID in not NULL )
  on (a.dpartment_ID = b.department_ID and a.employee_ID = 12345 )
the column name is not required to be same in joined two tables. Other condition operation than equal can be used in on clause.

3. where clause
   where a.department_ID = b.dpartment_ID
   where a.dpartment_ID = b.department_ID and a.employee_ID = b.employee_ID

where clause is implicit condition clause for join operation. It is used for row restriction for the final joined table. However its condition can be put to interval join operation to restrict row early and save time and space.

JOIN TYPE in Oracle 
1. JOIN
this is general join operation. It must be used with using or on clause.
2. INNER JOIN
INNER join must be used with using or on clause.
3. NATURAL JOIN
this join don't need using clause and cann't have on clause. I must use common column name and default equal conidtion.
4. NATURAL LEFT JOIN
this join is the same as natural join execpt that it displays not matched row from left side table.

5. NATURAL RIGHT JOIN
this join is the same as natural join execpt that it displays not matched row from right side table.
6. NATURAL FULL JOIN
this join is the same as natural join execpt that it displays not matched row from both side table

7. OUTER JOIN
outer jion must use with using clause and acts like natural join.

yli@TEST.corvus> select * from  department outer join department_head using (department_ID);

DEPARTMENT_ID DEPARTMENT_NAM EMPLOYEE_ID FIRST_NAME      LAST_NAME
------------- -------------- ----------- --------------- ---------------
            1 SALES                    2 Peter           Bank
            2 PRODUCT                  3 Scott           Levy
            4 CORP SERVICE             1 James           Smith

 select * from  department d outer join department_head dh on (d.department_id =dh.department_id );

select * from  department d outer join department_head dh on (d.department_id =dh.department_id )
                            *
ERROR at line 1:
ORA-00933: SQL command not properly ended



8  FULL OUTER JOIN ( FULL JOIN)
this join like inner join expect that it displays not matched row from both side table

9. LEFT OUTER JOIN (LEFT JOIN)
this join like inner join expect that it displays not matched row from left side table

10. RIGHT OUTER JOIN (RIGHT JOIN)
this join like inner join expect that it displays not matched row from right side table
11.CROSS JOIN
this cartesian join and can not have using and on clause.


THOUGHTS. 
1. Write SQL statement with explicit join conditions
2. Avoid using join and outter jion because their results is depends on conditions and kind of unpredictable.


reference:
http://en.wikipedia.org/wiki/Join_%28SQL%29
http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm
http://psoug.org/definition/JOIN.htm
http://tedytirta.com/oracle/to-using-natural-join-in-oracle/

No comments:

Post a Comment