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.

hive> use hiveql;

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

hive> create table employee(empid int, empname string , state string)
row format delimited
fields terminated by ',' ;

• Load the corresponding data into the table.

hive> load data local inpath '/home/codegyani/hive/employee' into table employee;

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

hive> create table employee_department(depid int, department_name string)
row format delimited
fields terminated by ',' ;

• Load the corresponding data into the table.

hive> load data local inpath '/home/codegyani/hive/employee_department' into table employee_department;

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

hive>select  e1.empname, e2.department_name from employee e1 join employee_department e2 on e1.empid= e2.depid;

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

hive> select  e1.empname, e2.department_name from employee e1 left outer join employee_department e2 on e1.empid= e2.depid;

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

hive> select  e1.empname, e2.department_name from employee e1 right outer join employee_department e2 on e1.empid= e2.depid;

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

select  e1.empname, e2.department_name from employee e1 full outer join employee_department e2 on e1.empid= e2.depid;