Specific usage instructions for mysql-joins

TABLE_A | TABLE_B
 PK Value | PK Value
---- ---------- | ---- ----------
 1 FOX | 1 TROT
 2 COP | 2 CAR
 3 TAXI | 3 CAB
 6 WASHINGTON | 6 MONUMENT
 7 DELL | 7 PC
 5 ARIZONA | 8 MICROSOFT
 4 LINCOLN | 9 APPLE
 10 LUCENT | 11 SCOTCH

join_table:
 table_reference JOIN table_factor [join_condition] //Inner join | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition //Outer join | table_reference LEFT SEMI JOIN table_reference join_condition //Left semi join | table_reference CROSS JOIN table_reference [join_condition] (as of Hive 0.10)


table_reference:
 table_factor //table | join_table //join statement table_factor:
 tbl_name [alias] //Table name [alias]
 | table_subquery alias //Subquery [alias]
 | ( table_references ) // table_reference with space


join_condition:
 ON expression //conditional statement starting with on

SELECT <select_list> 
FROM Table_A A
INNER JOIN Table_B B
ON A.Key = B.Key

-- 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

A_PK A_Value B_Value B_PK
---- ---------- ---------- ----
 1 FOX TROT 1
 2 COP CAR 2
 3 TAXI CAB 3
 6 WASHINGTON MONUMENT 6
 7 DELL PC 7

(5 row(s) affected)

SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key

-- 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

A_PK A_Value B_Value B_PK
---- ---------- ---------- ----
 1 FOX TROT 1
 2 COP CAR 2
 3 TAXI CAB 3
 4 LINCOLN NULL NULL
 5 ARIZONA NULL NULL
 6 WASHINGTON MONUMENT 6
 7 DELL PC 7
 10 LUCENT NULL NULL

(8 row(s) affected)

SELECT <select_list> 
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL

-- Left Excluding 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

A_PK A_Value B_Value B_PK
---- ---------- ---------- ----
 4 LINCOLN NULL NULL
 5 ARIZONA NULL NULL
 10 LUCENT NULL NULL
(3 row(s) affected)

SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key

--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

A_PK A_Value B_Value B_PK
---- ---------- ---------- ----
 1 FOX TROT 1
 2 COP CAR 2
 3 TAXI CAB 3
 6 WASHINGTON MONUMENT 6
 7 DELL PC 7
NULL NULL MICROSOFT 8
NULL NULL APPLE 9
NULL NULL SCOTCH 11

(8 row(s) affected)

SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL

-- Right Excluding 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

A_PK A_Value B_Value B_PK
---- ---------- ---------- ----
NULL NULL MICROSOFT 8
NULL NULL APPLE 9
NULL NULL SCOTCH 11

(3 row(s) affected)

SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key

-- Outer 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
FULL OUTER JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value B_Value B_PK
---- ---------- ---------- ----
 1 FOX TROT 1
 2 COP CAR 2
 3 TAXI CAB 3
 6 WASHINGTON MONUMENT 6
 7 DELL PC 7
NULL NULL MICROSOFT 8
NULL NULL APPLE 9
NULL NULL SCOTCH 11
 5 ARIZONA NULL NULL
 4 LINCOLN NULL NULL
 10 LUCENT NULL NULL

(11 row(s) affected)

SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL OR B.Key IS NULL

-- Outer Excluding 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
FULL OUTER JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL
OR B.PK IS NULL

A_PK A_Value B_Value B_PK
---- ---------- ---------- ----
NULL NULL MICROSOFT 8
NULL NULL APPLE 9
NULL NULL SCOTCH 11
 5 ARIZONA NULL NULL
 4 LINCOLN NULL NULL
 10 LUCENT NULL NULL

(6 row(s) affected)