Why does full outer join in HIVE gives weird result when one of the join fields is missing?
<p>I'm comparing the behavior between SQL engines.  Oracle has the behavior I would expect from a SQL engine for full outer joins:</p>

<h1>Oracle</h1>

<pre><code>CREATE TABLE sql_test_a 
( 
    ID         VARCHAR2(4000 BYTE), 
    FIRST_NAME VARCHAR2(200 BYTE), 
    LAST_NAME  VARCHAR2(200 BYTE) 
); 

CREATE TABLE sql_test_b 
( 
    NUM         VARCHAR2(4000 BYTE), 
    FIRST_NAME VARCHAR2(200 BYTE), 
    LAST_NAME  VARCHAR2(200 BYTE) 
); 

INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME) VALUES ('1', 'John', 'Snow'); 

INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME) VALUES ('2', 'Mike', 'Tyson'); 


INSERT INTO sql_test_b (NUM, FIRST_NAME, LAST_NAME) VALUES ('20', 'Mike', 'Tyson'); 
</code></pre>

<p>When I execute the following, it gives me the expected result.  The resulting table contains two rows, with one of the rows containing <code>NULL</code> for the <code>NUM</code> field, because there is no john snow in the table <code>sql_test_b</code>.</p>

<pre><code>SELECT A.FIRST_NAME, A.LAST_NAME, A.ID, B.NUM
FROM
SQL_TEST_A A
FULL OUTER JOIN
SQL_TEST_B B
ON 
A.FIRST_NAME = B.FIRST_NAME 
AND
A.LAST_NAME = B.LAST_NAME;
</code></pre>

<p>You can test the sql script here: <a href="http://sqltest.net/">http://sqltest.net/</a></p>

<h1>HIVE</h1>

<p>In HIVE, however, if you were to try the same thing, the full outer join results in a table with two rows.  The row that should be the "John Snow" row contains <code>NULL</code> for the fields FIRST_NAME, LAST_NAME, and NUM.  The <code>1</code> is filled in for <code>ID</code>, but that's it.  </p>

<p>Why such weird behavior in HIVE?  Is this a bug? Or am I missing something...because <em>Oracle 11g</em> seems to handle this much better.  Thanks.</p>