oracle join syntax examples

There are various joins in Oracle like inner join,left outer join,right outer join,full outer join,cross join. I will be giving oracle join syntax on each of them with example

Inner Join syntax and Example
Two tables examples and syntax

SELECT EMP.EMPNO,EMP.ENAME, DEPT.DEPTNO,DEPT.DNAME
FROM EMP , DEPT
where EMP. DEPTNO= DEPT. DEPTNO;

or

ANSI syntax

SELECT EMP.EMPNO,EMP.ENAME, DEPT.DEPTNO,DEPT.DNAME
FROM EMP INNER JOIN DEPT
on  EMP. DEPTNO= DEPT. DEPTNO;

or

SELECT EMP.EMPNO,EMP.ENAME, DEPT.DEPTNO,DEPT.DNAME
FROM EMP INNER JOIN DEPT
using (DEPTNO);

Example of Inner join Multiple tables

SELECT EMP.EMPNO,EMP.ENAME, DEPT.DEPTNO,DEPT.DNAME,REGION.REGION_NAME
FROM EMP , DEPT,REGION
where EMP. DEPTNO= DEPT. DEPTNO

and DEPT.REGION_ID=REGION.REGION_NAME;

or

SELECT EMP.EMPNO,EMP.ENAME, DEPT.DEPTNO,DEPT.DNAME,REGION.REGION_NAME
FROM EMP
inner join  DEPT   on  EMP. DEPTNO= DEPT. DEPTNO
inner join REGION on DEPT.REGION_ID=REGION.REGION_NAME;

or
SELECT EMP.EMPNO,EMP.ENAME, DEPT.DEPTNO,DEPT.DNAME,REGION.REGION_NAME
FROM EMP inner join  DEPT  using (DEPTNO)
inner join REGION using (REGION_ID);

Cross Join syntax and example

SELECT EMPNO,ENAME, DEPT.DEPTNO,DNAME  FROM EMP   , DEPT ;
or
SELECT EMPNO,ENAME, DEPT.DEPTNO,DNAME  FROM EMP   cross join DEPT ;

Left Outer Join syntax and example

select empno,ename,emp.deptno,dname
from emp
LEFT OUTER JOIN dept
on emp.deptno=dept.deptno;

  • sign syntax

select empno,ename,emp.deptno,dname
from emp ,dept where emp.deptno=dept.deptno(+) ;

  • is on the side where NULL is expected and it is on right hand side

Right Outer Join syntax and examples

select empno,ename,dept.deptno,dname
from emp
right OUTER JOIN dept
on emp.deptno=dept.deptno;

  • sign syntax

select empno,ename,dept.deptno,dname
from emp ,dept where emp.deptno(+)=dept.deptno ;

  • is on the side where NULL is expected and it is on left hand side

Full Outer Join syntax and examples

select empno,ename,dept.deptno,dname
from emp
full OUTER JOIN dept
on emp.deptno=dept.deptno;

Related Articles

What are Oracle Joins (Sql Joins)?

Nested Loop Join in Oracle 11g

Various Joins Method in Oracle

Hash join in Oracle with example

Leave a Reply