self join in oracle with examples

A self join is a join of 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 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

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

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

  1. Another example to extract the employee information which have same hire date

  1. 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

Table customer_bill

Customer_id
bill_amount
bill_month

table query

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

1111  1000 JAN

1111 2000 FEB

1111 999  MAR

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;

1111 1000   2000 999

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

Related Articles

What are Oracle Joins (Sql Joins)?

oracle join syntax examples

Various Joins Method in Oracle

Nested Loop Join in Oracle 11g

Hash join in Oracle with example

Leave a Reply