• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Sql » oracle join syntax examples

oracle join syntax examples

January 13, 2019 by techgoeasy Leave a Comment

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

Oracle Joins
Nested Loop Join in Oracle
Various Joins Method in Oracle
Hash join in Oracle

Filed Under: Oracle, Oracle Sql

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to check automatic statistics collection in Oracle
  • find segment name from block in oracle
  • How the sql query is executed in Oracle
  • How to do sql query tuning in Oracle
  • How to enable 10053 trace in Oracle

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us