oracle join syntax examples

Last updated on March 24th, 2019 at 01:38 pm

There are various joins in Oracle like inner join,left outer join,right outer join,full outer join,cross join. This SQL tutorial provides oracle join syntax on each of them with examples

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