Book Image

Apache Hive Essentials

By : Dayong Du
Book Image

Apache Hive Essentials

By: Dayong Du

Overview of this book

Table of Contents (17 chapters)
Apache Hive Essentials
Credits
About the Author
About the Reviewers
www.PacktPub.com
Preface
Index

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 left table, return null in the left table.

n

table_m

FULL [OUTER] JOIN

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