Home » Oracle » Oracle Sql » Self join in oracle with examples

Self join in oracle with examples

This Oracle SQL tutorial focuses on self join in oracle, and provides syntax, explanations, examples

A self join in Oracle is a join of oracle table to  itself. We can use the table multiple times .Each occurrence should have an alias name.Oracle database while executing the query join the table with itself and produce the result

General  syntax
select a.col1 ,b.col2  from mytable a,mytable b where <join condition> ;

Some Self Join in Oracle Example

(1) Presentation of Hierarchical data

We have a employee table and we want to specify the employee name , manager name for all the employee. We have a President also who does nor report  to anybody to show in the query

self join in oracle with examples

The below query with self join with outer join to extract the data in the desired format

self join in oracle with examples

Here are some of the points which is worth noticing the above query

a. We have used different alias for the multiple occurrence of the table EMP

b. We have used Left outer join to show the president information also

(2) Another example to extract the employee information which have same hire date

self join in oracle with examples

(3) We can use self join to transpose the rows in column also. We have a customer_bill table which have multiple rows for the rows for different month in the year and we want to show the bill amount for different month in column format

Desc customer_bill
--------------
Customer_id Varchar2(10)
bill_amount Number
bill_month Varchar2(10)

table query

select Customer_id,bill_amount,bill_month
from customer_bill where
customer_id=1111;

Query to transpose rows into column

select a.customer_id , b.bill_amount Jan-amount , 
c.bill_amount Feb-amount ,
c.bill_amount Mar-amount from customer_bill a ,customer_bill b,
customer_bill c where a.customer_id=b.customer_id ,
b.customer_id =c.customer_id 
and a.bill_month='JAN' 
and b.bill_month='FEB' and c.bill_month='MAR' 
and a.customer_id=1111;

Similarly  we can have multiple uses of the self join in oracle.  Please do let me know if you like this post

See also  How to Clone Oracle Application Server 10g

Related Articles
oracle sql join : What are Oracle Joins with examples (Sql joins), Different type of joins , Inner Join,Outer join,left outer join,right outer join,cross join with examples
oracle join syntax examples : Check out this post for oracle join syntax examples on inner join, cross join, outer join,left outer join,right outer join
Joins Method in Oracle : This post explain the various Plan Joins Method in Oracle, nested loop, sort merge join,Cartesian join, hash join to help understand
Nested Loop Join in Oracle 11g : Check out this page for all the details on Nested loop join in oracle,how Nested Loop Join in Oracle 11g is different, how nested loop join works in oracle
Hash join in Oracle:Check out this post for the detailed description of Hash join in Oracle, How it is different from Nested Loop join in oracle

Leave a Comment

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

Scroll to Top