HiveQL Join Types - ignacio-alorre/Hive GitHub Wiki

Source Code

We start with the following two tables:

Customers
Note: There is a duplicated record in this table

Segments
Note: There is one record with null value for the join key (segment) and two records with same join key

Returns only elements which join key (segment) is present in both tables

select c.*, o.discount from
D_customer c join D_offers o
on c.segment = o.segment

Returns all the records from both tables. Those rows without a join key (segment) in both tables will fill missing fields with null.

select c.*, o.discount from
D_customer c full join D_offers o
on c.segment = o.segment

Returns all elements from the left table. Where there is a match in the join key (segment) between both table, the record is enriched with attributes from the right table. Where there is no match, missing values are filled with null

select c.*, o.discount from
D_customer c left outer join D_offers o
on c.segment = o.segment

Returns those rows from the left table which join key (segment) is not present in the right table

select c.*, o.discount from
D_customer c join D_offers o
on c.segment = o.segment
where o.segment is null

Returns all elements from the right table. Where there is a match in the join key (segment) between both table, the record is enriched with attributes from the left table. Where there is no match, missing values are filled with null

select c.*, o.discount from
D_customer c right outer join D_offers o
on c.segment = o.segment

Returns those rows from the right table which join key (segment) is not present in the left table

select c.*, o.* from
D_customer c right outer join D_offers o
on c.segment = o.segment
where c.segment is null

Only elements which join key (segment) is present in just one of the tables

select c.*, o.discount from
D_customer c full outer join D_offers o
on c.segment = o.segment
where o.segment is null
or c.segment is null