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
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
(2) Another example to extract the employee information which have same hire date
(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
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