Different Hive Join Types and Examples

  • Post author:
  • Post last modified:January 6, 2020
  • Post category:BigData
  • Reading time:4 mins read

Join is a clause that is used for combining specific fields from two or more tables based on the common columns. The joins in the hive are similar to the SQL joins. Joins are used to combine rows from multiple tables. In this article, we will learn about different Hive join types with examples.

Read:

Below are the tables that we will be using to demonstrate different Join types in Hive:

Students:

students.id students.name students.city
1 ABC London 
2 BCD Mumbai 
3 CDE Bangalore 
4 DEF Mumbai 
5 EFG Bangalore

Dept:

dept.did dept.name dept.sid
100 CS 1 
101 Maths 1 
102 Physics 2 
103 Chem 3

Different Hive Join Types

Following are Different Hive Join Types

  • JOIN
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN

JOIN

It returns the matching rows from both the tables.

Syntax:

SELECT table1.col1, table2.col1 
FROM table2 
JOIN table1 
ON (table1.matching_col = table2. matching_col);

Results:

student_id student_name dept_id dept_name
1 ABC 100 CS 
1 ABC 101 Maths 
2 BCD 102 Physics 
3 CDE 103 Chem

Left Outer Join

This type of join returns all rows from the left table even if there is no matching row in the right table. Table returns all rows from the left table and matching rows from right table. Unmatched right tables records will be NULL.

Syntax:

SELECT table1. col1, table1.col2, table2.col1, table2.col2
FROM table1
LEFT OUTER JOIN table2
ON (table1.matching_col = table2.matching_col);

Result:

student_id student_name dept_id dept_name
1 ABC 100 CS 
1 ABC 101 Maths 
2 BCD 102 Physics 
3 CDE 103 Chem 
4 DEF NULL NULL
5 EFG NULL NULL

Right Outer Join

It returns all rows from the right table even if there is no matching row in the left table. Table returns all rows from the right table and matching rows from left table. Unmatched left table records will be NULL.

Read:

Syntax:

SELECT table1. col1, table1.col2, table2.col1, table2.col2
FROM table1 
RIGHT OUTER JOIN table2 ON (table1.matching_col = table2.matching_col);

Result:

student_id student_name dept_id dept_name
1 ABC 100 CS 
1 ABC 101 Maths 
2 BCD 102 Physics 
3 CDE 103 Chem

Full Outer Join

It returns all rows from the both tables that fulfill the JOIN condition. The unmatched rows from both tables will be returned as a NULL.

Syntax:

SELECT table1. col1, table1.col2, table2.col1, table2.col2
FROM table1 
FULL OUTER JOIN table2 ON (table1.matching_col = table2.matching_col);

Results:

student_id student_name dept_id dept_name
1 ABC 100 CS 
1 ABC 101 Maths 
2 BCD 102 Physics 
3 CDE 103 Chem 
4 DEF NULL NULL
5 EFG NULL NULL

Related Article

Tags: ,