Inner and Outer Join

Starting with the name of Allah the most merciful

Today we are going to see the various ways of joining tables.
Broadly their are two ways in which we can characterize these join types.

Inner Join--Inner join connects rows in two or more table if and only if
here are matched rows in all the tables being joined.In most of the cases their are only two tables
but its not always the case

Outer join--Outer join connects rows in two or more table in a way that is more inclusive,it means that unmatched row
will be included in the output.

First lets take on Inner join.
1)Inner join--
create table t11(id1 number,name1 varchar2(20));
create table t22(id2 number,name2 varchar2(20));

then you can join tables t1 and t2 like below

select id1,id2,name1,name2
from t11 inner join t22
on id1=id2;

--or you can say--

select id1,id2,name1,name2
from t11 join t22
on id1=id2;

By default join is inner join.So when you write only join it means inner join
So we are joining tables t11 and t22 based on the id1 and id2 values.

you can see that only two rows are in the resulting output after join.
since only two values of id1 from t11 and id2 from t22 (that is 1 and 2) matches.

There is another way to join tables without using the join keyword that is by using where clause.
select id1,id2,name1,name2
from t11,t22
where id1=id2;

--or with using table alias--

select tab1.id1,tab2.id2,tab1.name1,tab2.name2
from t11 tab1,t22 tab2
where tab1.id1=tab2.id2;

Both will produce the same output.

Note-- that only two rows are in the output ,there is no unmatched row in the output

now lets see how outer join is different from inner join

Outer Join)
Left Outer Join--It will show the unmatched rows of left side table of the join(left_table left outer join right_table)
Right Outer Join--It will show unmatched rows of right side table of the join(left_table right outer join right_table)
Full Outer Join--It will show both tables unmatched rows.

a)Left Outer Join
select id1,id2,name1,name2
from t11 left outer join t22    [you can omit the keyword outer here]
on id1=id2;

b)Right Outer Join
select id1,id2,name1,name2
from t11 right outer join t22    [again you can omit the keyword outer here]
on id1=id2;

c)Full Outer Join
select id1,id2,name1,name2
from t11 full outer join t22    [as before you can omit the keyword outer here]
on id1=id2;

Deprecated Outer Join Syntax(using + operator)
1)for left join
select id1,id2,name1,name2
from t11,t22
where id1=id2(+);

2)for right join
select id1,id2,name1,name2
from t11,t22
where  id1(+)=id2;