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

Get Apache Hive Essentials now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.