Oracle Joins
9i Joins:
Supports ANSI/ISO standard Sql 1999 syntax
Made easy for Appln s/w tools to understand Sql Queries
1. Natural Join
2. Join with Using
3. Join with ON
4. Inner Join
5. Left outer join
6. Right outer join
*7. Full outer join
8. Cross join
1. > select empno,ename,sal,job,deptno,dname,loc
from emp natural join 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 join
8. > select empno,ename,sal,job,deptno,dname,loc from emp cross join dept;
