• 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

Primary Sidebar

Top Posts & Pages

  • How to check Tablespace in Oracle -Size ,Free space,datafiles ,growth
  • how to check all constraints on a table in oracle
  • Query to check table size in Oracle database
  • Oracle dba interview questions and answers
  • Oracle Indexes and types of indexes in oracle with example



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 run Autoconfig on Patch system in R12.2
  • Admin scripts in R12.2 Ebuisness Suite
  • Timezone settings in Oracle database
  • how to enable trace in oracle apps r12
  • Transparent Data Encryption Queries

Copyright © 2021 : TechGoEasy

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