Understanding SQL Joins in Hive - INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN

3/12/2025

Diagram showing the types of SQL joins in Hive: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN

Go Back

Understanding SQL Joins in Hive: A Comprehensive Guide with Examples for Efficient Data Combining

Understanding SQL Joins with Examples

Introduction

SQL JOIN is a powerful clause used to combine data from multiple tables based on a related column between them. Joins help retrieve meaningful insights by merging records efficiently. This article explains different types of SQL joins with practical examples using developer and company tables.

Diagram showing the types of SQL joins in Hive: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN

Why Use Joins?

  • To combine data from multiple tables.
  • To fetch only relevant data efficiently.
  • To eliminate redundancy in database design.

SQL JOIN Syntax

JOIN table_reference 
   ON join_condition
   | table_reference {LEFT | RIGHT | FULL} [OUTER] JOIN table_reference
   ON join_condition
   | table_reference LEFT SEMI JOIN table_reference ON join_condition
   | table_reference CROSS JOIN table_reference;

Example Data

Let's assume we have two tables: developer and company.

Developer Table (developer)

name codingSkill experience
Alice Python 5 years
Bob Java 3 years
Carol SQL 7 years
Dave JavaScript 4 years

Company Table (company)

company_id name company_name
1 Alice Google
2 Bob Microsoft
3 Dave Amazon

Types of Joins in SQL

1. INNER JOIN (Default JOIN)

Retrieves matching records from both tables.

SELECT d.name, d.codingSkill, d.experience, c.company_name 
FROM developer d 
JOIN company c ON d.name = c.name;

Result:

name codingSkill experience company_name
Alice Python 5 years Google
Bob Java 3 years Microsoft
Dave JavaScript 4 years Amazon

2. LEFT OUTER JOIN

Retrieves all records from the left table (developer), along with matching records from the right table (company). If no match is found, NULL is returned.

SELECT d.name, d.codingSkill, d.experience, c.company_name 
FROM developer d 
LEFT JOIN company c ON d.name = c.name;

Result:

name codingSkill experience company_name
Alice Python 5 years Google
Bob Java 3 years Microsoft
Carol SQL 7 years NULL
Dave JavaScript 4 years Amazon

3. RIGHT OUTER JOIN

Retrieves all records from the right table (company), along with matching records from the left table (developer). If no match is found, NULL is returned.

SELECT d.name, d.codingSkill, d.experience, c.company_name 
FROM developer d 
RIGHT JOIN company c ON d.name = c.name;

Result:

name codingSkill experience company_name
Alice Python 5 years Google
Bob Java 3 years Microsoft
Dave JavaScript 4 years Amazon

4. FULL OUTER JOIN

Retrieves all records from both tables. If no match is found, NULL is returned.

SELECT d.name, d.codingSkill, d.experience, c.company_name 
FROM developer d 
FULL OUTER JOIN company c ON d.name = c.name;

Result:

name codingSkill experience company_name
Alice Python 5 years Google
Bob Java 3 years Microsoft
Carol SQL 7 years NULL
Dave JavaScript 4 years Amazon

Choosing the Right JOIN Type

JOIN Type Description
INNER JOIN Returns only matching records in both tables.
LEFT JOIN Returns all records from the left table and matched records from the right table.
RIGHT JOIN Returns all records from the right table and matched records from the left table.
FULL JOIN Returns all records from both tables.

Conclusion

Using SQL joins allows you to retrieve meaningful data from multiple tables efficiently. Depending on the requirement, you can use INNER JOIN for exact matches, LEFT JOIN for keeping all left-side records, RIGHT JOIN for keeping all right-side records, or FULL OUTER JOIN for combining everything.

Mastering SQL joins will help you handle complex queries and manage relational databases effectively.

Stay Tuned for More!

For more SQL tutorials and database optimization techniques, visit   'www.developerindian.com'

Table of content