The OUTER JOIN and CROSS JOIN statements
Besides INNER JOIN, Hive also supports regular OUTER JOIN and FULL JOIN. The logic of such
JOIN is the same to what's in the RDBMS. The following table summarizes the differences of a common JOIN:
|
Common JOIN type |
Logic |
Rows returned (assume table_m has m rows and table_n has n rows) |
|---|---|---|
|
|
This returns all rows matched in both tables. |
m ∩ n |
|
|
This returns all rows in the left table and matched rows in the right table. If there is no match in the right table, return null in the right table. |
m |
|
|
This returns all rows in the right table and matched rows in the left table. If there is no match in the left table, return null in the left table. |
n |
|
|
This returns all rows in both the tables and matched rows in both the tables. If there is no match in the left or right table, return null instead. |
m + n - m ... |
