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/
