Javatpoint Logo
Javatpoint Logo

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:

HiveQL - JOIN
HiveQL - JOIN

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.

HiveQL - JOIN

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.

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

HiveQL - JOIN
  • Load the corresponding data into the table.

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

HiveQL - JOIN
  • Load the corresponding data into the table.

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

HiveQL - JOIN
HiveQL - JOIN
HiveQL - JOIN

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.

HiveQL - JOIN

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: -

HiveQL - JOIN
HiveQL - JOIN
HiveQL - JOIN

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.

HiveQL - JOIN

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: -

HiveQL - JOIN
HiveQL - JOIN
HiveQL - JOIN

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.

HiveQL - JOIN

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: -

HiveQL - JOIN
HiveQL - JOIN
HiveQL - JOIN
Next TopicHive




Help Others, Please Share

facebook twitter google plus pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA