Example of merge ,hash and nested join

Could Any body Please give me one example of each of three types of joins that is
Merge Join
Hash Join
Nested join

I am not quite sure if I understood you right.
You can force the join algorithm by specifying a join hint. For example, the join:

/*Nested loop join*/

select * from Orders O
inner loop join Customers C on
C.fCUSTOMER = O.fCUSTOMER
will be inner loop join, regardless of whether the optimizer considers it the best or not. Indeed, you can force hash or merge join either by specifying "hash" or "merge" instead of loop.
Maybe you want a description of how the SQL performs those joins? Or, you want a scenario where a specified type of join is most likely to be chosen by the optimizer?

|||

Thanks Andranik Khachatryan But You haven't understood the question.

|||

Please search in BOL for the above keywords and you will topics that explain each join type. Below are two queries to show nested loop & hash joins & another with a forced merge join. The merge join is harder to repro because you will have to come up with some data/joins that will require that. And since query optimization is a cost-based decision it may not always be easy to come up with examples.

create table t1 ( i int not null )
create table t2 ( i int not null )
go
set showplan_text on
go
select*
from t1
join t2
on t1.i = t2.i
go
set showplan_text off
go

alter table t1 add primary key ( i )
alter table t2 add primary key ( i )
go

set showplan_text on
go
select*
from t1
join t2
on t1.i = t2.i
go

set showplan_text off
go

set showplan_text on
go
select*
from t1
join t2
on t1.i = t2.i
option(merge join)
go

set showplan_text off
go

drop table t1, t2

You probably want to read the topics that I mentioned since that will give insight as to when/why a particular join was picked by the optimizer.