Some Join Examples (INNER,OUTER)

Declare @T1 Table(Eid int,Ename varchar(10))


Declare @T2 Table(Eid int,Ename varchar(10))

INSERT INTO @T1(Eid,EName)Values(1,'A')

INSERT INTO @T1(Eid,EName)Values(1,'B')

INSERT INTO @T2(Eid,EName)Values(1,'A')

INSERT INTO @T2(Eid,EName)Values(1,'B')

INSERT INTO @T2(Eid,EName)Values(1,'C')

SELECT a.Eid, a.EName from @T1 a INNER JOIN @T2 b ON a.Eid = b.Eid
 
RESULT :-
**********************************************************
Eid EName


----------- ----------

1 A

1 A

1 A

1 B

1 B

1 B
****************************************************************
NEXT :-
****************************************************************
Declare @T1 Table(Eid int,Ename varchar(10))


Declare @T2 Table(Eid int,Ename varchar(10))

INSERT INTO @T1(Eid,EName)Values(1,'A')

INSERT INTO @T1(Eid,EName)Values(2,'B')

INSERT INTO @T2(Eid,EName)Values(1,'A')

INSERT INTO @T2(Eid,EName)Values(2,'B')

INSERT INTO @T2(Eid,EName)Values(3,'C')

INSERT INTO @T2(Eid,EName)Values(4,'D')

SELECT a.Eid, a.EName from @T1 a INNER JOIN @T2 b ON a.Eid = b.Eid

SELECT a.Eid, a.EName from @T1 a LEFT OUTER JOIN @T2 b ON a.Eid = b.Eid

SELECT a.Eid, a.EName from @T1 a RIGHT OUTER JOIN @T2 b ON a.Eid = b.Eid

*********************************************************************
RESULT1 :-
*********
Eid         EName


----------- ----------

1           A

2           B
********************
RESULT 2 :-
*************
Eid     EName


----------- ----------

1       A

2       B
**************
Result 3 :-
**********
Eid                EName


----------- ----------

1                 A

2                 B

NULL        NULL

NULL        NULL