HIVE JOINS

JOIN as the name suggests is used to join multiple tables based on a common column between them.

Types of JOINS

Inner Join : Fetches the rows which are common to both tables.

Left Join : Fetches all rows from the left table and only common rows from the right one.

Right Join : Fetches all rows from the right table and only common rows from the left one.

Full Outer Join : Fetches all rows from the left and right table if there is a common row.

Left Semi Join : Fetches rows only from the left table after matching the key column with the right table.

Note : Hive only supports Equi-Joins. i.e. Joins can only be performed with equal(=) sign only. Greater than(>), less than (<) or not equal
(< >) in join is not supported in HIVE.

INNER JOIN

The inner join returns only those records which are common to both tables.

Syntax:

SELECT column1,column2
FROM table1
INNER JOIN table2
ON table1.column1 = table2.column1;

Inner Join Example:

Employee table

emp_id	name	city	salary
101	John	California	50000
102	Tom	Mumbai	40000
103	Harry	Delhi	80000
104	Rahul	Bangalore	60000
105	Sakil	Delhi	90000

Skill table

skill_id	skill_name	emp_id
404	Java/J2EE	103
405	Hadoop	101
406	C/C++	105
407	Oracle	102
408	Spring	101

Example:

SELECT Employee.emp_id, Employee.name, Skill.skill_name
FROM Employee
INNER JOIN Department
ON Employee.emp_id = Skill.emp_id;

Output:

emp_id	name	dept_name
101	John	Hadoop
101	John	Hadoop
102	Tom	Oracle
103	Harry	Java/J2EE
105	Sakil	C/C++

In the above example we have taken all the rows from the right table and only the matching rows from the left table.

LEFT JOIN

The left join fetches all rows from the left table and only common rows from the right one.

Syntax:

SELECT column1,column2
FROM table1
LEFT JOIN table2
ON table1.column1 = table2.column1;

Left Join Example:

Employee table

emp_id	name	city	salary
101	John	California	50000
102	Tom	Mumbai	40000
103	Harry	Delhi	80000
104	Rahul	Bangalore	60000
105	Sakil	Delhi	90000

Skill table

skill_id	skill_name	emp_id
404	Java/J2EE	103
405	Hadoop	101
406	C/C++	105
407	Oracle	102
408	Spring	101

Example:

SELECT Employee.emp_id, Employee.name, Skill.skill_name
FROM Employee
LEFT JOIN Skill
ON Employee.emp_id = Skill.emp_id;

Output:

emp_id	name	skill_name
101	John	Hadoop
102	Tom	Oracle
103	Harry	Java/J2EE
104	Rahul	null
105	Sakil	C/C++

In the above example we have taken all the rows from the left table and only the matching rows from the right table. Well we have found out Rahul is the only guy who doesn't have any skill set.

RIGHT JOIN

The right join fetches all rows from the right table and only common rows from the left one.

Syntax:

SELECT column1,column2
FROM table1
RIGHT JOIN table2
ON table1.column1 = table2.column1;

Right Join Example:

Employee table

emp_id	name	city	salary
101	John	California	50000
102	Tom	Mumbai	40000
103	Harry	Delhi	80000
104	Rahul	Bangalore	60000
105	Sakil	Delhi	90000

Skill table

skill_id	skill_name	emp_id
404	Java/J2EE	103
405	Hadoop	101
406	C/C++	105
407	Oracle	102
408	Spring	101

Example:

SELECT Employee.emp_id, Employee.name, Skill.skill_name
FROM Employee
RIGHT JOIN Skill
ON Employee.emp_id = Skill.emp_id;

Output:

emp_id	name	skill_name
101	John	Hadoop
102	Tom	Oracle
103	Harry	Java/J2EE
105	Sakil	C/C++

In the above example we have taken all the rows from the left table and only the matching rows from the right table. Well we have found out Rahul is the only guy who doesn't have any skill set.

Full Outer Join

The Full Outer Join fetches all rows from the left and right table if there is a common rowin either the left table or the right table.

Syntax:

SELECT column1,column2
FROM table1
FULL OUTER JOIN table2
ON table1.column1 = table2.column1;

Full Outer Join Example:

Employee table

emp_id	name	city	salary
101	John	California	50000
102	Tom	Mumbai	40000
103	Harry	Delhi	80000
104	Rahul	Bangalore	60000
105	Sakil	Delhi	90000

Skill table

skill_id	skill_name	emp_id
404	Java/J2EE	103
405	Hadoop	101
406	C/C++	105
407	Oracle	102
408	Spring	101

Example:

SELECT Employee.emp_id, Employee.name, Skill.skill_name
FROM Employee
FULL OUTER JOIN Department
ON Employee.emp_id = Skill.emp_id;

Output:

emp_id	name	skill_name
103	Harry	Java/J2EE
101	John	Hadoop
105	Sakil	C/C++
102	Tom	Oracle
101	John	Spring
104	Rahul	null

In the above example we have combined two tables Employee and Skill to check which all employees have what skill set.

Left Semi Join

The Left Semi Join fetches rows only from the left table after matching the key column with the right table. The difference between left join and left semi join is left join fetches all the column from left table and also the matching contents of right table is displayed but left semi join fetches records from the left table only which matches with the key column.

Syntax:

SELECT column1,column2
FROM table1
LEFT SEMI JOIN table2
ON table1.column1 = table2.column1;

Left Semi Join Example:

Employee table

emp_id	name	city	salary
101	John	California	50000
102	Tom	Mumbai	40000
103	Harry	Delhi	80000
104	Rahul	Bangalore	60000
105	Sakil	Delhi	90000

Skill table

skill_id	skill_name	emp_id
404	Java/J2EE	103
405	Hadoop	101
406	C/C++	105
407	Oracle	102
408	Spring	101

Example:

SELECT *
FROM Employee
LEFT SEMI JOIN Department
ON Employee.emp_id = Skill.emp_id;

Output:

emp_id	name	city	salary
101	John	California	50000
102	Tom	Mumbai	40000
103	Harry	Delhi	80000
105	Sakil	Delhi	90000

In the above example, only the matching columns of the left table are displayed.

❮❮ PREV NEXT ❯❯