SQL JOIN statements with examples

INNER JOIN or EQUI JOIN:

INNER JOIN :
select ex_details.name,ex_dept.dcode,ex_details.salary from ex_details inner join ex_dept on ex_details.dcode = ex_dept.dcode;

EXECUTION PALN FOR JOIN :

Explain Plan for select e.name,d.dname,l.loc_name from ex_details e, ex_dept d, ex_country l
where e.dcode=d.dcode and e.loc_id=l.loc_id;

NON-EQUI JOIN:
select e.name,g.grade from ex_details e, ex_grade g where e.salary between g.min_sal and g.max_sal;

SELF JOIN:
select e.code,e.name,m.name from ex_emp e,ex_emp m where e.code=m.manager_id;

OUTER JOIN:
select e.name,d.dname from ex_details e, ex_dept d where e.dcode(+)=d.dcode;
note : Orcale Propriatry. It will display unmatched records from department.
or
select e.name,d.dname from ex_details e right outer join ex_dept d on e.dcode=d.dcode;
note :SQL 99 syntax

CROSS JOIN:
select name,dname from ex_details CROSS JOIN ex_dept;

NATURAL JOIN: **very useful**
Note: Always perform equi join. oracle checks Data Dictionary, it will check the table structure of both tables.
RULE: Coulmn Name and Data Type should be exactly same.
Natural join will not help if you have multiple column. Anyways it will perform based on first column in first table.

select name,dname from ex_details NATURAL JOIN ex_dept;
Note : Only Matched records are displayed.

Comments :
1. USING clause, still works even though Column Data Type mis-match. Conversion happens. Column name should be same. To force the natural join to use specific column then go for: USING CLAUSE
2. If you using NATURAL JOIN in syntax it wont work. If you are using USING clause then use only JOIN.

select e.ename,d.dname from ex_nj1 e JOIN ex_nj2 d USING(MCODE); {Column -Explicit, Condition -implicit}

Note: If column names are different use "ON". {Column -Explicit, Condition -Explicit}
select e.ename,d.dname from from ex_test1 e JOIN ex_test2 d ON(e.mcode=d.m_code);

select e.name,d.dname,l.loc_name
from ex_details e
JOIN ex_dept d ON d.dcode=e.dcode
JOIN ex_country l ON l.loc_id=e.loc_id