/* Join examples from :
* http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
*/

DROP TABLE Table_A;
Drop TABLE Table_B;

CREATE TABLE Table_A
(
  PK INT NOT NULL,
  Value VARCHAR(10)
)
;

CREATE TABLE Table_B
(
  PK INT NOT NULL,
  Value VARCHAR(10)
)
;

INSERT INTO Table_A
(PK, Value) VALUES (1, 'FOX')
;

INSERT INTO Table_A
(PK, Value) VALUES (2, 'COP')
;

INSERT INTO Table_A
(PK, Value) VALUES (3, 'TAXI')
;

INSERT INTO Table_A
(PK, Value) VALUES (4, 'LINCOLN')
;

INSERT INTO Table_A
(PK, Value) VALUES (5, 'ARIZONA')
;

INSERT INTO Table_A
(PK, Value) VALUES (6, 'WASHINGTON')
;

INSERT INTO Table_A
(PK, Value) VALUES (7, 'DELL')
;

INSERT INTO Table_A
(PK, Value) VALUES (10, 'LUCENT')
;

INSERT INTO Table_B
(PK, Value) VALUES (1, 'TROT')
;

INSERT INTO Table_B
(PK, Value) VALUES (2, 'CAR')
;

INSERT INTO Table_B
(PK, Value) VALUES (3, 'CAB')
;

INSERT INTO Table_B
(PK, Value) VALUES (6, 'MONUMENT')
;

INSERT INTO Table_B
(PK, Value) VALUES (7, 'PC')
;

INSERT INTO Table_B
(PK, Value) VALUES (8, 'MICROSOFT')
;

INSERT INTO Table_B
(PK, Value) VALUES (9, 'APPLE')
;

INSERT INTO Table_B
(PK, Value) VALUES (11, 'SCOTCH')
;

/* 'INNER JOIN' */
SELECT A.PK AS A_PK, A.Value AS A_Value,
       B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
INNER JOIN Table_B B
ON A.PK = B.PK;

/* 'LEFT JOIN' */
SELECT A.PK AS A_PK, A.Value AS A_Value,
       B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
LEFT JOIN Table_B B
ON A.PK = B.PK;

/* 'RIGHT JOIN' */
SELECT A.PK AS A_PK, A.Value AS A_Value,
       B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
RIGHT JOIN Table_B B
ON A.PK = B.PK;

/* 'OUTER JOIN' */
SELECT *       
FROM 
(
	(SELECT A.PK AS A_PK, A.Value AS A_Value,
			B.Value AS B_Value, B.PK AS B_PK 
		FROM Table_A A LEFT  OUTER JOIN Table_B B ON A.PK = B.PK
	)
    UNION
	(SELECT A.PK AS A_PK, A.Value AS A_Value,
			B.Value AS B_Value, B.PK AS B_PK
		FROM Table_A A RIGHT OUTER JOIN Table_B B ON A.PK = B.PK
	)
) Table_AB 
;

/* 'LEFT JOIN EXCLUDING INNER JOIN' */
SELECT A.PK AS A_PK, A.Value AS A_Value,
       B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
LEFT JOIN Table_B B
ON A.PK = B.PK
WHERE B.PK IS NULL;

/* 'RIGHT JOIN EXCLUDING INNER JOIN' */
SELECT A.PK AS A_PK, A.Value AS A_Value,
       B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
RIGHT JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL;

/* 'OUTER JOIN EXCLUDING INNER JOIN' */
/* 'OR' */
/* 'LEFT & RIGHT JOINS EXCLUDING INNER JOIN' */

SELECT *       
FROM 
(
	(SELECT A.PK AS A_PK, A.Value AS A_Value,
			B.Value AS B_Value, B.PK AS B_PK 
		FROM Table_A A LEFT  OUTER JOIN Table_B B ON A.PK = B.PK
	)
    UNION
	(SELECT A.PK AS A_PK, A.Value AS A_Value,
			B.Value AS B_Value, B.PK AS B_PK
		FROM Table_A A RIGHT OUTER JOIN Table_B B ON A.PK = B.PK
	)
) Table_AB    

WHERE A_PK IS NULL
OR B_PK IS NULL;
