See: http://www.morningglorytech.com/SQLServer.html for more info.

Paste these queries into SQLServer 2008 Management Studio:

Right outer joins match the bottom with the top substituting nulls for missing top colums:

SELECT A.col1,B.col1

FROM

((select 'A11' col1,'A12' col2)

union

(select 'A21' col1,'A22' col2)) A

RIGHT OUTER JOIN

((select 'B11' col1,'B12' col2)

union

(select 'B21' col1,'B22' col2)

union

(select 'A11' col1,'A12' col2)

union

(select 'A21' col1,'A22' col2)) B

ON A.col1 = B.col1

A11	A11
A21	A21
NULL	B11
NULL	B21

Left outer joins match the top with the bottom inserting nulls for missing bottom columns:

SELECT A.col1,B.col1

FROM

((select 'B11' col1 'B12' col2)

union

(select 'B21' col1,'B22' col2)

union

(select 'A11' col1,'A12' col2)

union

(select 'A21' col1,'A22' col2)) B

LEFT OUTER JOIN

((select 'A11' col1, 'A12' col2)

union

(select 'A21' col1 'A22' col2)) A

ON A.col1 = B.col1

A11	A11
A21	A21
NULL	B11
NULL	B21

NULLs from outer joins can be used to tell if rows in one table don`t exist in another much faster than NOT EXISTS (uses indexes not scans):

SELECT C.A_COL1, C.B_COL2

FROM

(SELECT A.col1 A_COL1, B.col1 B_COL2

FROM

((select 'A11' col1, 'A12' col2)

union

(select 'A21' col1,'A22' col2)) A

RIGHT OUTER JOIN

((select 'B11' col1, 'B12' col2)

union

(select 'B21' col1, 'B22' col2)

union

(select 'A11' col1, 'A12' col2)

union

(select 'A21' col1,'A22' col2)) B

ON A.col1 = B.col1) C

WHERE C.A_COL1 IS NULL

NULL	B11
NULL	B21

There is no left or right for inner joins and the top is always matched to the bottom:

SELECT A.col1, B.col1

FROM

((select 'A11' col1, 'A12' col2)

union

(select 'A21' col1,'A22' col2)) A

INNER JOIN

((select 'B11' col1, 'B12' col2)

union

(select 'B21' col1,'B22' col2)

union

(select 'A11' col1,'A12' col2)

union

(select 'A21' col1 ,'A22' col2)) B

ON A.col1 = B.col1

A11	A11
A21	A21