SQL Joins

/*  Behavior of Joins by Type (Inner, Left Outer, Right Outer, Full Outer)  */

/*  Build Table1 (left) and Table2 (right) and populate
    Note particular exclusion of certain records, to show effect of imbalance by join type */

declare @table1 table (id int, letter char)
insert into @table1 values (1, 'a')
insert into @table1 values (2, 'b')
--insert into @table1 values (3, 'c')
insert into @table1 values (4, 'd')
insert into @table1 values (5, 'e')
insert into @table1 values (6, 'f')
--insert into @table1 values (7, 'g')
insert into @table1 values (8, 'h')

declare @table2 table (id int, letter char)
insert into @table2 values (1, 'z')
insert into @table2 values (2, 'y')
insert into @table2 values (3, 'x')
insert into @table2 values (4, 'w')
--insert into @table2 values (5, 'v')
insert into @table2 values (6, 'u')
insert into @table2 values (7, 't')
insert into @table2 values (8, 's')


-- Select all records from each table to show full data set

select * from @table1

/*  Result:
id          letter
----------- ------
1           a
2           b
4           d
5           e
6           f
8           h
*/

select * from @table2

/* Result:
id          letter
----------- ------
1           z
2           y
3           x
4           w
6           u
7           t
8           s
*/

-- Inner Join - Select only record that exist in both tables
select * from @table1 t1 inner join @table2 t2 on t1.id = t2.id

/* Result:
id          letter id          letter
----------- ------ ----------- ------
1           a      1           z
2           b      2           y
4           d      4           w
6           f      6           u
8           h      8           s
*/

-- Left Outer Join - Select everything from Table1, regardless of whether it has a match in Table2
select * from @table1 t1 left outer join @table2 t2 on t1.id = t2.id

/* Result:
id          letter id          letter
----------- ------ ----------- ------
1           a      1           z
2           b      2           y
4           d      4           w
5           e      NULL        NULL
6           f      6           u
8           h      8           s
*/

-- Right Outer Join - Select everything from Table2, regardless of whether it has a match in Table1
select * from @table1 t1 right outer join @table2 t2 on t1.id = t2.id

/* Result:
id          letter id          letter
----------- ------ ----------- ------
1           a      1           z
2           b      2           y
NULL        NULL   3           x
4           d      4           w
6           f      6           u
NULL        NULL   7           t
8           h      8           s
*/

-- Full Outer Join - Select everything from both tables, lining up whatever has a match
select * from @table1 t1 full join @table2 t2 on t1.id = t2.id

/* Result:
id          letter id          letter
----------- ------ ----------- ------
1           a      1           z
2           b      2           y
4           d      4           w
5           e      NULL        NULL
6           f      6           u
8           h      8           s
NULL        NULL   3           x
NULL        NULL   7           t
*/