Tutorial corner

Informatica,ETL,oracle,sql/plsql

Type of Oracle join with Example | Oracle SQL join

Spread the love

Type of Oracle join with Example : An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them. Join can be used in SELECT,UPDATE , DELETE statements. In below article we will go through the different type of SQL Joins (Inner Join , Outer Join,Self Join ,Cross Join , Natural Join) used in Oracle with examples.

Type of Oracle join with Example

Type of Oracle join with Example

Type of Oracle join with Example

Check here for Sample SQL data.

A. EMP Table Data:

EMPNO ENAME DEPTNO MGR
4 HOOVER
5 LINCOLN 4 4
8 GRANT 5 4
9 JACKSON 4 5
10 FILLMORE 2 5
14 ROOSEVELT 1 8

B. DEPT Table Data

DEPTNO DNAME
1 ACCOUNTING
2 RESEARCH
3 SALES
4 OPERATIONS

1. Oracle SQL Equi Join

Properties of  Oracle Equi Join :

  • It a simple join  where columns are joined with equal (=) operator
  • Two Type of Equi Joins are there
    1. Oracle Inner Join
    2. Outer Join

1.1. Oracle SQL Inner Join :

Properties of  Oracle SQL Inner Join

  • Most Commonly used join.
  • Combine records from two table which satisfy joining condition

Syntax of  Oracle  SQL Inner Join

SELECT table1.column1 , table1.column2,table1.column3 , table2.column1…
FROM table1
INNER JOIN table
ON table1.column3=table2.column2

Example of  Oracle  SQL  Inner Join

SELECT emp.empno, emp.ename ,emp.job, dept.deptno,dept.dname
FROM emp
INNER JOIN dept
ON emp.deptno= dept.deptno;

OLD Syntax Oracle  SQL  Inner Join

SELECT emp.empno, emp.ename ,emp.job, dept.deptno,dept.dname
FROM emp,dept
Where emp.deptno= dept.deptno ;

Output  Oracle  SQL  Inner Join

EMPNO ENAME DEPTNO DNAME
14 ROOSEVELT 1 ACCOUNTING
10 FILLMORE 2 RESEARCH
9 JACKSON 4 OPERATIONS
5 LINCOLN 4 OPERATIONS

Visualization Oracle  SQL  Inner Join

SQL Inner Join

SQL Inner Join

1.2. Oracle SQL Left Outer Join :

Properties of  Oracle Left Outer Join

  • List all records from Left table and matching records from Right table.

Syntax of Oracle Left Outer Join

SELECT table1.column1 , table1.column2,table1.column3 , table2.column1…
FROM table1
LEFT [ OUTER ] JOIN table
ON table1.column3=table2.column2;

 

Example of  Oracle Left Outer Join

 

SELECT emp.empno, emp.ename ,emp.job, dept.deptno,dept.dname
FROM emp
LEFT OUTER JOIN dept
ON emp.deptno= dept.deptno;

 

OLD Syntax Oracle Left Outer Join

 

SELECT emp.empno, emp.ename ,emp.job, dept.deptno,dept.dname
FROM emp,dept
Where emp.deptno= dept.deptno(+);

 

Output of Oracle Left Outer Join

EMPNO ENAME DEPTNO DNAME
14 ROOSEVELT 1 ACCOUNTING
10 FILLMORE 2 RESEARCH
9 JACKSON 4 OPERATIONS
5 LINCOLN 4 OPERATIONS
4 HOOVER
8 GRANT

Visualization Oracle Left Outer Join

SQL left outer Join

SQL left outer Join

1.3. Oracle SQL Right Outer Join :

Properties of  Oracle SQL Right Outer Join :

  • List all records from Right table and matching records from Left table.

Syntax of  Oracle SQL Right Outer Join

SELECT table1.column1 , table1.column2,table1.column3 , table2.column1…
FROM table1
RIGHT [ OUTER ] JOIN table
ON table1.column3=table2.column2;

 

Example of  Oracle SQL Right Outer Join

 

SELECT emp.empno, emp.ename ,emp.job, dept.deptno,dept.dname
FROM emp
RIGHT OUTER JOIN dept
ON emp.deptno= dept.deptno;

 

OLD Syntax  of  Oracle SQL Right Outer Join

 

SELECT emp.empno, emp.ename ,emp.job, dept.deptno,dept.dname
FROM emp,dept
Where emp.deptno(+) = dept.deptno;

Output of  Oracle SQL Right Outer Join

EMPNO ENAME DEPTNO DNAME
14 ROOSEVELT 1 ACCOUNTING
10 FILLMORE 2 RESEARCH
3 SALES
9 JACKSON 4 OPERATIONS
5 LINCOLN 4 OPERATIONS

Visualization of  Oracle SQL Right Outer Join

SQL right outer Join

SQL right outer Join

1.4. Oracle SQL Full Outer Join :

Properties of Oracle SQL Full Outer Join

  • List all records from Right table and Left table both
  • Non Matched records are shown as NULL

Syntax of Oracle SQL Full Outer Join

 

SELECT table1.column1 , table1.column2,table1.column3 , table2.column1…
FROM table1
FULL [ OUTER ] JOIN table
ON table1.column3=table2.column2;

 

Example of Oracle SQL Full Outer Join

SELECT emp.empno, emp.ename ,emp.job, dept.deptno,dept.dname
FROM emp
FULL OUTER JOIN dept
ON emp.deptno= dept.deptno;

 

OLD Syntax of Oracle SQL Full Outer Join

Full Outer cannot be join directly in Old syntax , but it can be written with the help of Union query.

Output of Oracle SQL Full Outer Join

EMPNO ENAME DEPTNO DNAME
14 ROOSEVELT 1 ACCOUNTING
10 FILLMORE 2 RESEARCH
3 SALES
9 JACKSON 4 OPERATIONS
5 LINCOLN 4 OPERATIONS
4 HOOVER
8 GRANT

Visualization of Oracle SQL Full Outer Join

SQL full outer join

SQL full outer join

EMPNO ENAME DEPTNO DNAME
14 ROOSEVELT 1 ACCOUNTING
10 FILLMORE 2 RESEARCH
3 SALES
9 JACKSON 4 OPERATIONS
5 LINCOLN 4 OPERATIONS
4 HOOVER
8 GRANT  5

 

2.  Oracle SQL Non equi joins

Properties of Oracle SQL Non Equi Join :

  • Uses comparison operator other than (=) operator , example >, < , >= ,<=, <>

Syntax of Oracle SQL Non Equi Join

SELECT table1.column1 , table1.column2,table1.column3 , table2.column1…
FROM table1 ,table2
WHERE table1.column3 > table2.column2;

Example of Oracle SQL Non Equi Join

SELECT emp.empno, emp.ename ,emp.job, emp.deptno,dept.deptno,dept.dname
FROM emp,dept
WHERE emp.deptno> dept.deptno;

Output of  Oracle SQL Non Equi Join

EMPNO ENAME DEPTNO DEPTNO DNAME
10 FILLMORE 2 1 ACCOUNTING
9 JACKSON 4 1 ACCOUNTING
9 JACKSON 4 2 RESEARCH
9 JACKSON 4 3 SALES
5 LINCOLN 4 1 ACCOUNTING
5 LINCOLN 4 2 RESEARCH
5 LINCOLN 4 3 SALES
8 GRANT 5 1 ACCOUNTING
8 GRANT 5 2 RESEARCH
8 GRANT 5 3 SALES
8 GRANT 5 4 OPERATIONS

 

3. Oracle  SQL Self  Joins:

Properties of Oracle SQL Self Join:

  • A Self Join is a type of sql join which is used to join a table to itself
  • Normally use to show the parent child relation of the records
  • It is necessary to  use alias for both copies of the table to avoid column ambiguity.

Syntax of Oracle SQL Self Join

SELECT a.column1 , a.column2,a.column3 , b.column1…
FROM table1 a ,table1 b
WHERE a.column1 = b.column2;

Example of Oracle SQL Self Join :

SELECT a.empno, a.ename ,a.job, a.deptno,a.mgr,b.ename  mgr_name
FROM emp a,emp b
WHERE a.mgr = b.empno;

 

Output of Oracle SQL Self Join

EMPNO ENAME DEPTNO MGR MGR_NAME
5 LINCOLN 4 4 HOOVER
8 GRANT 5 4 HOOVER
9 JACKSON 4 5 LINCOLN
10 FILLMORE 2 5 LINCOLN
14 ROOSEVELT 1 8 GRANT

4.  Oracle SQL Natural Joins :

Properties of Oracle SQL Natural Join :

  • A Natural Join compare all column name automatically (which have same column name)

Syntax of Oracle SQL Natural Join:

 

SELECT table1.column1 , table1.column2,table1.column3 , table2.column1…
FROM table1
NATURAL JOIN table2;

 

Example of Oracle SQL Natural Join

SELECT emp.empno, emp.ename ,emp.job, dept.deptno,dept.dname
FROM emp
NATURAL JOIN dept;

 

Output of Oracle SQL Natural Join:

EMPNO ENAME DEPTNO DNAME
14 ROOSEVELT 1 ACCOUNTING
10 FILLMORE 2 RESEARCH
9 JACKSON 4 OPERATIONS
5 LINCOLN 4 OPERATIONS

 

5.  Oracle SQL Cross Joins :

Properties of  Oracle SQL Cross Join

  • A Cross Join gives the all combination of records from table 1 and table 2
  • Also known as Cartesian product
  • It basically gives the cross product of two table .Example table1 contains 3 records and table2 contains 4 then cross join will give 3 X 4 = 12 records

Syntax of  Oracle SQL Cross Join

SELECT table1.column1 , table1.column2,table1.column3 , table2.column1…
FROM table1
CROSS JOIN table2;

Example of  Oracle SQL Cross Join

SELECT emp.empno, emp.ename ,emp.job, dept.deptno,dept.dname
FROM emp
CROSS JOIN dept;

Output of  Oracle SQL Cross Join

EMPNO ENAME DEPTNO DEPTNO DNAME
4 HOOVER 1 ACCOUNTING
4 HOOVER 2 RESEARCH
4 HOOVER 3 SALES
4 HOOVER 4 OPERATIONS
10 FILLMORE 2 1 ACCOUNTING
10 FILLMORE 2 2 RESEARCH
10 FILLMORE 2 3 SALES
10 FILLMORE 2 4 OPERATIONS
14 ROOSEVELT 1 1 ACCOUNTING
14 ROOSEVELT 1 2 RESEARCH
14 ROOSEVELT 1 3 SALES
14 ROOSEVELT  1 4 OPERATIONS
9 JACKSON 4 1 ACCOUNTING
9 JACKSON 4 2 RESEARCH
9 JACKSON 4 3 SALES
9 JACKSON 4 4 OPERATIONS
5 LINCOLN 4 1 ACCOUNTING
5 LINCOLN 4 2 RESEARCH
5 LINCOLN 4 3 SALES
5 LINCOLN 4 4 OPERATIONS
8 GRANT 5 1 ACCOUNTING
8 GRANT 5 2 RESEARCH
8 GRANT 5 3 SALES
8 GRANT 5 4 OPERATIONS

Where are Cartesian Joins used in real life?

In 95% of the cases, Cartesian products originate from accidental cross join operations and cause unnecessary high load on a database. Maybe the results aren’t even wrong, as someone may have applied a UNION or a DISTINCT keyword, to remove unwanted duplicates.

But there are those 5% of SQL queries, where the Cartesian product is intentional, even desirable

If you have a “grid” that you want to populate completely, like size and color information for a particular article of clothing:

select
size,
color
from
sizes CROSS JOIN colors

Maybe you want a table that contains a row for every minute in the day, and you want to use it to verify that a procedure has executed each minute, so you might cross three tables:

select
hour,
minute
from
hours CROSS JOIN minutes

So in above tutorial, we have gone through the Type of Oracle join with Example along with their syntax, properties and detailed example. We discussed about Oracle full outer join,oracle inner sql join, inner join in oracle, left outer join in oracle,right outer join in oracle.

Updated: April 23, 2016 — 5:17 pm

The Author

Amit Kumar

Hello Friends , I am Amit Kumar working in Accenture .I love to write article on latest technologies like Informatica , ETL , data warehouse , SQL-PL SQL
Copyright 2015 - Tutorial Corner Frontier Theme