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