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