Types of Joins in Oracle with Examples

9i Joins:Supports ANSI/ISO standard Sql 1999 syntaxMade easy for Appln s/w tools to understand Sql Queries1. Natural Join 2. Join with Using 3. Join with ON 4. Inner Join 5. Left outer join6. Right outer join*7. Full outer join8. Cross join1. > select empno,ename,sal,job,deptno,dname,loc from emp
dept;2. > select empno,ename,sal,job,deptno,dname,loc from emp join dept using(deptno);3. > select e.empno, e.ename, e.sal, e.job, e.deptno, d.dname, d.loc from emp e Join dept d on(e.deptno = d.deptno) ;4. > select e.empno, e.ename, e.sal, e.job, e.deptno,d.dname, d.loc from emp e Inner Join dept d on(e.deptno = d.deptno) ;5. > select e.empno, e.ename, e.sal, e.job, e.deptno,d.dname, d.loc from emp e left outer join dept d on(e.deptno = d.deptno) ;6. > select e.empno, e.ename, e.sal, e.job, e.deptno,d.dname, d.loc from emp e right outer join dept d on(e.deptno = d.deptno) ;* 7. > select e.empno, e.ename, e.sal, e.job, e.deptno,d.dname, d.loc from emp e full outer join dept d on(e.deptno = d.deptno) ; ** left outer join union right outer join = full outer join8. > select empno,ename,sal,job,deptno,dname,loc from emp cross join dept;

moreless