Inner join in Oracle

There are different ways of joining tables in Oracle, it all depends to choose upon the way we need it.

Note: - We usually try to skip or not bother about type of joining. We are just bothered whether query runs fine with out errors. It is definitely wrong.
We get the data from query which also depends upon the type of join if there are more than one table or required self join.


i) Inner join.
ii) Self join.
iii) Cross join.
iv) Full outer join.
v) Left outer join.
vi) Right outer join.

1) Inner Join

This join is usually what we use for much kind of requirements.
Ex:-
Consider
There is an organisation which has many employees working in it.
We have a table “tbl_employees” which has all the data related to each employee who are working currently and as well as who have resigned or left the organisation.
So this table has the total employee’s data that are active as well as not active.
No we have one more table”tbl_employees pay”, which stores the pay’s made to the employee every month.
So obviously this table will not have the records related to employees who are not active or who have left the organisation.

Table structures.

Tbl_employees

Emp_ID number,
Emp_Name varchar(200),
Emp_Email varchar(200),
.
.
Emp_Status varchar(1) default ‘A’
(Comments :- A-Active, I – Inactive)


Tbl_employees_pay

Emp_ID number,
Emp_Sal number,
Paid_date date,
Paid_for_month month,
Paid_for_year year.

Data :-

Tbl_employees

Tbl_employees_pay

(Employees with ID 3 and 5 have resigned or quitted organisation before February month in 2008, so there are no pays fr 3 and 5 in February
Employees with ID 1 and 2 are the only employees available in December 2007 (big organisation)
)

Now the requirement is to show data about number of pays made by organisation to each employee and total amount to each employee in a year.

Query (using inner join):-

select a.emp_id,a.emp_name,count(*) no_pays,sum(emp_sal)as total_pay from tbl_employees a,tbl_employees_pay b where
a.emp_id=b.emp_id and b.paid_for_year=2008group by a.emp_id,a.emp_name order by a.emp_id


(or)

select a.emp_id,a.emp_name,count(*) no_pays,sum(emp_sal)as total_pay from tbl_employees a
inner join tbl_employees_pay b on
a.emp_id=b.emp_id where b.paid_for_year=2008group by a.emp_id,a.emp_name order by a.emp_id

The above 2 queries give the same result set or data.
We can observe there is no “Inner join” clause in the first query where as available in second query.
The condition “where a.emp_id=b.emp_id” in first query, it self does the same kind of execution plan that inner join does which mean it is same as inner join.


For year 2008

Output :-

Query:-

(Total pays made not for year but as a whole).
select a.emp_id,a.emp_name,count(*) no_pays,sum(emp_sal)as total_pay from tbl_employees a
inner join tbl_employees_pay b on
a.emp_id=b.emp_id group by a.emp_id,a.emp_name order by a.emp_id

(We can observe there is no condition for paid_for_year=2008, so it considers all pays made.
For which emp_id’s 1 and 2 have on 2007 december.

Output :-