Types of Joins in Oracle with Examples

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;