Apache Hive Essentials

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)
table_m JOIN table_n	This returns all rows matched in both tables.	m ∩ n
table_m LEFT [OUTER] JOIN table_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
table_m RIGHT [OUTER] JOIN table_n	This returns all rows in the right table and matched rows in the left table. If there is no match in the ...