Join #

Description #

JOIN is used to combine rows from two relations based on join condition.

Syntax #

Hive Dialect supports the following syntax for joining tables:

join_table:
    table_reference [ INNER ] JOIN table_factor [ join_condition ]
  | table_reference { LEFT | RIGHT | FULL } [ OUTER ] JOIN table_reference join_condition
  | table_reference LEFT SEMI JOIN table_reference [ ON expression ] 
  | table_reference CROSS JOIN table_reference [ join_condition ]
 
table_reference:
    table_factor
  | join_table
 
table_factor:
    tbl_name [ alias ]
  | table_subquery alias
  | ( table_references )
 
join_condition:
    { ON expression | USING ( colName [, ...] ) }

JOIN Type #

INNER JOIN #

INNER JOIN returns the rows matched in both join sides. INNER JOIN is the default join type.

LEFT JOIN #

LEFT JOIN returns all the rows from the left join side and the matched values from the right join side. It will concat the values from both sides.
If there’s no match in right join side, it will append NULL value. LEFT JOIN is equivalent to LEFT OUTER JOIN.

RIGHT JOIN #

RIGHT JOIN returns all the rows from the right join side and the matched values from the left join side. It will concat the values from both sides.
If there’s no match in left join side, it will append NULL value. RIGHT JOIN is equivalent to RIGHT OUTER JOIN.

FULL JOIN #

FULL JOIN returns all the rows from both join sides. It will concat the values from both sides.
If there’s one side does not match the row, it will append NULL value. FULL JOIN is equivalent to FULL OUTER JOIN.

LEFT SEMI JOIN #

LEFT SMEI JOIN returns the rows from the left join side that have matching in right join side. It won’t concat the values from the right side.

CROSS JOIN #

CROSS JOIN returns the Cartesian product of two join sides.

Examples #

-- INNER JOIN
SELECT t1.x FROM t1 INNER JOIN t2 USING (x);
SELECT t1.x FROM t1 INNER JOIN t2 ON t1.x = t2.x;

-- LEFT JOIN
SELECT t1.x FROM t1 LEFT JOIN t2 USING (x);
SELECT t1.x FROM t1 LEFT OUTER JOIN t2 ON t1.x = t2.x;

-- RIGHT JOIN
SELECT t1.x FROM t1 RIGHT JOIN t2 USING (x);
SELECT t1.x FROM t1 RIGHT OUTER JOIN t2 ON t1.x = t2.x;

-- FULL JOIN
SELECT t1.x FROM t1 FULL JOIN t2 USING (x);
SELECT t1.x FROM t1 FULL OUTER JOIN t2 ON t1.x = t2.x;

-- LEFT SEMI JOIN
SELECT t1.x FROM t1 LEFT SEMI JOIN t2 ON t1.x = t2.x;

-- CROSS JOIN
SELECT t1.x FROM t1 CROSS JOIN t2 USING (x);