Database Reference
In-Depth Information
Notice that the row for Ali is now returned, and the columns from the
things
table are
NULL
because there is no match.
Hive also supports right outer joins, which reverses the roles of the tables relative to the
left join. In this case, all items from the
things
table are included, even those that wer-
en't purchased by anyone (a scarf):
hive>
SELECT sales.*, things.*
>
FROM sales RIGHT OUTER JOIN things ON (sales.id = things.id);
Joe 2 2 Tie
Hank 2 2 Tie
Hank 4 4 Coat
Eve 3 3 Hat
NULL NULL 1 Scarf
Finally, there is a full outer join, where the output has a row for each row from both tables
in the join:
hive>
SELECT sales.*, things.*
>
FROM sales FULL OUTER JOIN things ON (sales.id = things.id);
Ali 0 NULL NULL
NULL NULL 1 Scarf
Hank 2 2 Tie
Joe 2 2 Tie
Eve 3 3 Hat
Hank 4 4 Coat
Semi joins
Consider this
IN
subquery, which finds all the items in the
things
table that are in the
sales
table:
SELECT *
FROM things
WHERE things.id IN (SELECT id from sales);
We can also express it as follows:
hive>
SELECT *
>
FROM things LEFT SEMI JOIN sales ON (sales.id = things.id);
2 Tie
4 Coat
3 Hat
