Pinal Dave's Basics of Joins (T-SQL) - see http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/

basic_joins_t.sql

/* 
   Pinal Dave's Basics of Joins (T-SQL)
   translated to @table from #table, no adventureworks, comment column for ease of comparison
   http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/ 
*/

declare @table1 TABLE (ID INT, Value VARCHAR(10))
INSERT INTO @table1 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'

declare @table2 TABLE (ID INT, Value VARCHAR(10))
INSERT INTO @table2 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 6,'Sixth'
UNION ALL
SELECT 7,'Seventh'
UNION ALL
SELECT 8,'Eighth'

 -- uncomment if you need to see the source tables
 -- note that @table2 is missing 4 & 5 by design
--SELECT *
--FROM @table1
--SELECT *
--FROM @table2

/* INNER JOIN */
SELECT t1.*,t2.* , '' as 'inner join'
FROM @table1 t1
INNER JOIN @table2 t2 ON t1.ID = t2.ID

/* LEFT JOIN */
SELECT t1.*,t2.*, '' as 'left join'
FROM @table1 t1
LEFT JOIN @table2 t2 ON t1.ID = t2.ID

/* RIGHT JOIN */
SELECT t1.*,t2.*, '' as 'right join'
FROM @table1 t1
RIGHT JOIN @table2 t2 ON t1.ID = t2.ID

/* OUTER JOIN */
SELECT t1.*,t2.*, '' as 'outer join'
FROM @table1 t1
FULL OUTER JOIN @table2 t2 ON t1.ID = t2.ID

/* LEFT JOIN - WHERE NULL */
SELECT t1.*,t2.*, '' as 'left join where NULL'
FROM @table1 t1
LEFT JOIN @table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

/* RIGHT JOIN - WHERE NULL */
SELECT t1.*,t2.*, '' as 'right join where NULL'
FROM @table1 t1
RIGHT JOIN @table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL

/* OUTER JOIN - WHERE NULL */
SELECT t1.*,t2.*, '' as 'outer join where NULL'
FROM @table1 t1
FULL OUTER JOIN @table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL OR t2.ID IS NULL

/* CROSS JOIN */
SELECT t1.*,t2.*, '' as 'cross join'
FROM @table1 t1
CROSS JOIN @table2 t2