Do you have trouble remembering the difference between an inner, outer and full join? You are not alone. Even I get them confused; so I put together this simple little list to remind me; maybe it will help you.
declare @x1 table(col1 int, col2 int)
insert into @x1
select 11, 1 UNION ALL
select 11, 2 UNION ALL
select 11, 3 UNION ALL
select 11, 4 UNION ALL
select 11, 5 UNION ALL
select 11, 6
declare @x2 table(col2 int, col3 int)
insert into @x2
select 3, 12 UNION ALL
select 4, 12 UNION ALL
select 5, 12 UNION ALL
select 6, 12 UNION ALL
select 7, 12 UNION ALL
select 8, 12
print 'data in x1'
select * from @x1
print 'data in x2'
select * from @x2
print 'inner join, only matching col2'
select
col1, x1.col2 x1Xol2, x2.col2 x2Col2, col3
from
@x1 x1
join @x2 x2 on x1.col2 = x2.col2
print 'left outer join, all x1 col2'
select
col1, x1.col2 x1Xol2, x2.col2 x2Col2, col3
from
@x1 x1
left outer join @x2 x2 on x1.col2 = x2.col2
print 'right outer join, all x2 col2'
select
col1, x1.col2 x1Xol2, x2.col2 x2Col2, col3
from
@x1 x1
right outer join @x2 x2 on x1.col2 = x2.col2
print 'full outer join, all x1/x2 col2'
select
col1, x1.col2 x1Xol2, x2.col2 x2Col2, col3
from
@x1 x1
full outer join @x2 x2 on x1.col2 = x2.col2
print 'cross join, all possible combinations'
select
col1, x1.col2 x1Xol2, x2.col2 x2Col2, col3
from
@x1 x1
cross join @x2 x2
/*
data in x1
col1 col2
----------- -----------
11 1
11 2
11 3
11 4
11 5
11 6
(6 row(s) affected)
data in x2
col2 col3
----------- -----------
3 12
4 12
5 12
6 12
7 12
8 12
(6 row(s) affected)
inner join, only matching col2
col1 x1Xol2 x2Col2 col3
----------- ----------- ----------- -----------
11 3 3 12
11 4 4 12
11 5 5 12
11 6 6 12
(4 row(s) affected)
left outer join, all x1 col2
col1 x1Xol2 x2Col2 col3
----------- ----------- ----------- -----------
11 1 NULL NULL
11 2 NULL NULL
11 3 3 12
11 4 4 12
11 5 5 12
11 6 6 12
(6 row(s) affected)
right outer join, all x2 col2
col1 x1Xol2 x2Col2 col3
----------- ----------- ----------- -----------
11 3 3 12
11 4 4 12
11 5 5 12
11 6 6 12
NULL NULL 7 12
NULL NULL 8 12
(6 row(s) affected)
full outer join, all x1/x2 col2
col1 x1Xol2 x2Col2 col3
----------- ----------- ----------- -----------
11 1 NULL NULL
11 2 NULL NULL
11 3 3 12
11 4 4 12
11 5 5 12
11 6 6 12
NULL NULL 7 12
NULL NULL 8 12
(8 row(s) affected)
cross join, all possible combinations
col1 x1Xol2 x2Col2 col3
----------- ----------- ----------- -----------
11 1 3 12
11 2 3 12
11 3 3 12
11 4 3 12
11 5 3 12
11 6 3 12
11 1 4 12
11 2 4 12
11 3 4 12
11 4 4 12
11 5 4 12
11 6 4 12
11 1 5 12
11 2 5 12
11 3 5 12
11 4 5 12
11 5 5 12
11 6 5 12
11 1 6 12
11 2 6 12
11 3 6 12
11 4 6 12
11 5 6 12
11 6 6 12
11 1 7 12
11 2 7 12
11 3 7 12
11 4 7 12
11 5 7 12
11 6 7 12
11 1 8 12
11 2 8 12
11 3 8 12
11 4 8 12
11 5 8 12
11 6 8 12
(36 row(s) affected)
*/