Self join in Oracle

It is some thing like a table joining to itself in need.
There could be situation where we need to use the same table again in the query to have specific business data.
Let us consider example:-

We have “tbl_employees_pay” table, which has all the pays made by organisation to each employee every month.

Data:-

Tbl_employees_pay

Requirement:-

List out the total percentage made by organisation to each employee in a year.
What to do?
1) Find the total pay made by organisation each year.
2) Find the total pay made to each employee in a year and divide it with the value from step1.


Query:-


select emp_id,sum(tab1.emp_sal) as totsalyr_emp,round((sum(tab1.emp_sal)/tab.totsal)*100,2) as per_sal,tab.totsal as totsalyr,tab1.paid_for_year from tbl_employees_pay tab1,
(select sum(emp_sal) as totsal,paid_for_year from tbl_employees_pay
group by paid_for_year)tab
where tab.paid_for_year=tab1.paid_for_year
group by emp_id,tab1.paid_for_year,tab.totsal
order by tab1.paid_for_year,emp_id

In the above query we can observe, that the same table is used twice to calculate total pays in a year and employee per year.
This is also called as sub query but a table joining to it self.
Self join condition from the above query,

where tab.paid_for_year=tab1.paid_for_year

Output: