Using a cross join
In this recipe, you will learn how to use a cross join in Hive.
Cross join, also known as Cartesian product, is a way of joining multiple tables in which all the rows or tuples from one table are paired with the rows and tuples from another table. For example, if the left-hand side table has 10 rows and the right-hand side table has 13 rows then the result set after joining the two tables will be 130 rows. That means all the rows from the left-hand side table (having 10 rows) are paired with all the tables from the right-hand side table (having 13 rows).
If there is a WHERE clause in the SQL statement that includes a cross join, then first the cross join takes place and then the result set is filtered out with the help of the WHERE clause. This means cross joins are not an efficient and optimized way of joining the tables.
The general syntax of a cross join is as follows:
join_condition | table_reference [CROSS] JOIN table_reference join_condition
Where:
table_reference: Is...
