O'Reilly logo

Apache Hive Essentials by Dayong Du

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required