HiveQL - JOIN

The HiveQL Join clause is used to combine the data of two or more tables based on a related column between them. The various type of HiveQL joins are: -

• Inner Join
• Left Outer Join
• Right Outer Join
• Full Outer Join

Here, we are going to execute the join clauses on the records of the following table:

Inner Join in HiveQL

The HiveQL inner join is used to return the rows of multiple tables where the join condition satisfies. In other words, the join criteria find the match records in every table being joined.

Example of Inner Join in Hive

In this example, we take two table employee and employee_department. The primary key (empid) of employee table represents the foreign key (depid) of employee_department table. Let's perform the inner join operation by using the following steps: -

• Select the database in which we want to create a table.

• Now, create a table by using the following command:

• Load the corresponding data into the table.

• Now, create another table by using the following command:

• Load the corresponding data into the table.

• Now, perform the inner join operation by using the following command: -

Left Outer Join in HiveQL

The HiveQL left outer join returns all the records from the left (first) table and only that records from the right (second) table where join criteria find the match.

Example of Left Outer Join in Hive

In this example, we perform the left outer join operation.

• Let's us execute the left outer join operation by using the following command: -

Right Outer Join in HiveQL

The HiveQL right outer join returns all the records from the right (second) table and only that records from the left (first) table where join criteria find the match.

Example of Left Outer Join in Hive

In this example, we perform the left outer join operation.

• Let's us execute the left outer join operation by using the following command: -

Full Outer Join

The HiveQL full outer join returns all the records from both the tables. It assigns Null for missing records in either table.

Example of Full Outer Join in Hive

In this example, we perform the full outer join operation.

• Let's us execute the full outer join operation by using the following command: -