sql server join with example

create PROCEDURE dbo.join_test
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
begin


declare  @tbl1   table (id numeric(10),[name] varchar(50))

declare  @tbl2   table (id numeric(10),[name] varchar(50) )

insert into @tbl1 values(1,'a')
insert into @tbl1 values(2,'b')
insert into @tbl1 values(3,'c')
insert into @tbl1 values(4,'d')
insert into @tbl1 values(5,'e')

insert into @tbl2 values(1,'p')
insert into @tbl2 values(2,'q')
insert into @tbl2 values(6,'r')
insert into @tbl2 values(7,'s')
insert into @tbl2 values(8,'t')

-- select a.id , a.name,b.id,b.name  from @tbl1 as a left outer join  @tbl2 as b on a.id=b.id

-- select a.id , a.name,b.id,b.name  from @tbl1 as a right outer join  @tbl2 as b on a.id=b.id

-- select a.id , isnull (a.name,'no name'),b.id,b.name  from @tbl1 as a full outer join  @tbl2 as b on a.id=b.id

-- select a.id , a.name,b.id,b.name  from @tbl1 as a full outer join  @tbl2 as b on a.id=b.id where a.id is null or b.id is null

-- select a.id , a.name,b.id,b.name  from @tbl1 as a cross join  @tbl2 as b where a.id=b.id

end