Different Types Of Joins In Oracle

Join is a method used to combine two or more tables,views or materialized views based on a common condition. There is wide variety of classification. Below given in one such classification.

Different types of Joins

1. Cross Join/Cartesian Product
2. Natural Join
3. Inner Join
4. Outer join
a.Left Outer Join
b.Right Join
c.Full Outer Join
5. Anti Join
6. Semi Join
7. Self Join
8. Equi Join and Non Equi Join

Lets see an illustrative example with two tables emp_tab and dep_tab.

create table dep_tab(
depid number(3) primary key,
depname varchar(20));

create table emp_tab(
lastname varchar2(20),
depid number(3) references dep_tab(depid));

insert into dep_tab values(&dep_id,’&depname’);
insert into emp_tab values(‘&lastname’,&dep_id);

These are the two tables. One emp_tab which contains the employee details.Other dep_tab which contains the department details.Insert values as you wish.

1. Cross Join/Cartesian Product- This join lists all the rows in all tables. No condition is specified here.

select lastname,dep_tab.depid,depname
from emp_tab,dep_tab;

2.Natural Join- This is something similar to cross join. But eliminates duplicate values in the output.

select * from emp_tab natural join dep_tab;

3.Inner join- This join returns all the rows from the both the tables where there is a match

select * from emp_tab inner join dep_tab
on emp_tab.depid=dep_tab.depid;

OR

select * from emp_tab,dep_tab
where dep_tab.depid=emp_tab.depid;

4. Outer Join – This join returns all the rows from one table and only those rows from second table which meets the condition.
Classified as
a.Left Outer Join
b.Right Outer Join
c.Full Outer Join

a. Left Outer Join- Returns all the rows from left table(ie, first table) and rows that meet the condition from second table.

select * from emp_tab left outer join dep_tab
on emp_tab.depid=dep_tab.depid;

OR

select * from emp_tab, dep_tab
where emp_tab.depid=dep_tab.depid(+);

b.Right Outer Join- Returns all the rows from right table(ie, second table) and rows that meet the condition from first table.

select * from emp_tab right outer join dep_tab
on emp_tab.depid=dep_tab.depid;

OR

select * from emp_tab, dep_tab
where emp_tab.depid(+)=dep_tab.depid;

c.Full Outer Join – Combines the results of both left and right outer joins.

select * from emp_tab full outer join dep_tab
on emp_tab.depid=dep_tab.depid;

5. Anti Join – It returns rows from the first table where no matches are found in the second table

select * from emp_tab where depid not in
(select depid from dep_tab where depname = ‘MGMNT’)
order by lastname;

6. Semi Join – It returns rows from the first table where one or more matches are found in the second table.

SELECT * FROM dep_tab
WHERE EXISTS
(SELECT * FROM emp_tab
WHERE dep_tab.depid = emp_tab.depid and dep_tab.depid!=35)
ORDER BY depname;

7. Self join -Here the table is joined (compared) to itself.

select a.lastname,a.depid,b.depid
from emp_tab a,emp_tab b
where a.lastname=b.lastname;
8. Equi Join and Non Equi Join-Equi Join is a kind of join where condition used is equal(=) sign. And Non Equi Join uses all comparison operators except the equal (=) operator. Like >=, <=, <, >.

Equi Joinselect * from emp_tab,dep_tab
where dep_tab.depid=emp_tab.depid;

Non Equi Joinselect * from emp_tab,dep_tab
where dep_tab.depid !=35;