Join Our 4-Week Free Gen AI Course with select Programs.

Request a callback

or Chat with us on

Joins in DBMS – Different Types Explained with Examples

Basics of SQL
Basics of SQL
icon
12 Hrs. duration
icon
12 Modules
icon
2600+ Learners
logo
Start Learning

Wonder how it is possible to extract data from several tables within a database? How can we, during such an extract, combine data from these tables effectively? These are common questions when working with databases.

 

Joins in DBMS are a solution since they allow for the combining of data from many tables in related columns. This is important to acquire relevant results and insight that will, in turn, help in making relevant decisions.

 

Joins are very similar to adhesives that hold your data together. It will indeed be extremely difficult to retrieve data from more than one table in case there are no joins.

 

Let’s dive into the world of joins in DBMS and see how they work.

Detailed Explanation of Inner Join in DBMS

Inner join is one of the most commonly used joins in DBMS. It joins rows from two or more tables based on some common fields or columns.

 

In an inner join, we end up with only those rows that find a match with other rows in the table. This means we are not going to have any rows that don’t match anything in the second table.

 

Example:

Consider two tables, Students and Courses:

 

Students Table:

student_id name age
1 Neha 20
2 Murali 22
3 Divyansh 19

 

Courses Table:

course_id student_id course_name
101 1 Math
102 2 Science
103 4 History

 

SQL Query:

SELECT Students.name, Courses.course_name FROM Students INNER JOIN Courses ON Students.student_id = Courses.student_id;

Output:

name course_name
Neha Math
Murali Science

 

In this example, we get only the students who have enrolled in courses. Students without courses are not included.

 

Also Read: What is a Database Management System?

Comprehensive Overview of Theta Join in DBMS

Theta join is more flexible than the inner join. It allows us to join tables based on any condition, not just equality.

We can use any comparison operator such as >, <, >=, <=, or !=.

 

Example:

Consider two tables, Employees and Departments:

 

Employees Table:

emp_id name salary dept_id
1 Divyansh 50000 10
2 Krish 60000 20
3 Neha 55000 30

 

Departments Table:

dept_id dept_name min_salary
10 HR 45000
20 IT 55000
30 Sales 52000

 

SQL Query:

SELECT Employees.name, Departments.dept_name FROM Employees JOIN Departments ON Employees.salary >= Departments.min_salary;

Output:

name dept_name
Divyansh HR
Krish IT
Neha Sales

 

Here, we get employees who meet or exceed the minimum salary requirement for their department.

Equi Join in DBMS: Concepts and Examples

Equi join is a type of theta join where the condition is always based on equality. It’s similar to an inner join but explicitly uses the equality operator.

 

Example:

Consider two tables, Authors and Books:

 

Authors Table:

author_id author_name
1 Mark Twain
2 Jane Austen
3 J.K. Rowling

 

Books Table:

book_id title author_id
101 Tom Sawyer 1
102 Pride and Prejudice 2
103 Harry Potter 3

 

SQL Query:

SELECT Authors.author_name, Books.title FROM Authors INNER JOIN Books ON Authors.author_id = Books.author_id;

Output:

author_name title
Mark Twain Tom Sawyer
Jane Austen Pride and Prejudice
J.K. Rowling Harry Potter

 

In this example, we get a list of authors and their books. Only matching rows based on author_id are included.

DevOps & Cloud Engineering
Internship Assurance
DevOps & Cloud Engineering

Natural Join in DBMS Explained

Have you ever wondered how we can merge tables without specifying the join condition? Natural join does just that.

 

Natural join combines tables based on columns with the same name and domain. It’s like matching puzzle pieces that fit perfectly.

 

Example:

Consider two tables, Employees and Departments:

 

Employees Table:

emp_id name dept_id
1 Suman 10
2 Sonali 20
3 Aditi 30

 

Departments Table:

dept_id dept_name
10 HR
20 IT
30 Sales

 

SQL Query:

SELECT Employees.name, Departments.dept_name FROM Employees NATURAL JOIN Departments;

Output:

name dept_name
Suman HR
Sonali IT
Aditi Sales

 

In this example, we get a list of employees with their department names. Natural join uses the dept_id column to match rows from both tables.

Understanding Outer Joins in DBMS

Outer joins help us retrieve matching records from related tables and include non-matching rows.

Left Outer Join Explained with Examples

The left outer join returns all rows from the left table and matching rows from the right table. Non-matching rows in the right table are filled with NULL.

 

Example:

Consider two tables, Customers and Orders:

 

Customers Table:

customer_id customer_name
1 Murali
2 Divyansh
3 Anupriya

 

Orders Table:

order_id customer_id product_name
101 1 Laptop
102 2 Phone
103 4 Tablet

 

SQL Query:

SELECT Customers.customer_name, Orders.product_name

FROM Customers

LEFT JOIN Orders

ON Customers.customer_id = Orders.customer_id;

 

Output:

customer_name product_name
Murali Laptop
Divyansh Phone
Anupriya NULL

 

In this example, we see that Alex has no matching order, so the product name is NULL.

Right Outer Join Detailed with Examples

The right outer join returns all rows from the right table and matching rows from the left table. Non-matching rows in the left table are filled with NULL.

 

Example:

Consider two tables, Products and Suppliers:

 

Products Table:

product_id product_name
1 Laptop
2 Phone
3 Tablet

 

Suppliers Table:

supplier_id product_id supplier_name
101 1 Supplier A
102 2 Supplier B
103 4 Supplier C

 

SQL Query:

SELECT Products.product_name, Suppliers.supplier_name

FROM Products

RIGHT JOIN Suppliers

ON Products.product_id = Suppliers.product_id;

 

Output:

product_name supplier_name
Laptop Supplier A
Phone Supplier B
NULL Supplier C

 

Here, we see that Supplier C has no matching product, so the product name is NULL.

Conclusion

Joins in DBMS are crucial for combining data from multiple tables. They help us retrieve meaningful insights and perform complex queries efficiently. We explored different types of joins:

 

  • Inner join: Matches rows based on related columns.
  • Theta join: Joins tables based on any condition.
  • Equi join: A type of theta join using equality.
  • Natural join: Matches columns with the same name and domain.
  • Outer joins: Include non-matching rows (left and right outer joins).

Understanding these joins helps us manage and query databases effectively.

FAQs
The primary purpose is to combine data from two or more tables based on related columns, enabling efficient data retrieval.
Inner join returns only the rows that have matching values in both tables. Outer join returns all the rows from one or both joined tables, filling in NULL for non-matching rows.
Use a theta join when you need to join tables based on conditions other than equality, using comparison operators like >, <, >=, etc.
A natural join automatically matches columns with the same name and domain, while an equi join explicitly uses equality conditions to match specified columns.
Yes, we can use multiple joins in a single query to combine data from more than two tables, specifying the conditions for each join operation.

Deploying Applications Over the Cloud Using Jenkins

Prashant Kumar Dey

Prashant Kumar Dey

Associate Program Director - Hero Vired

Ex BMW | Google

24 October, 7:00 PM (IST)

Limited Seats Left

Book a Free Live Class

left dot patternright dot pattern

Programs tailored for your success

Popular

Management

Data Science

Finance

Technology

Future Tech

Upskill with expert articles

View all
Hero Vired logo
Hero Vired is a leading LearnTech company dedicated to offering cutting-edge programs in collaboration with top-tier global institutions. As part of the esteemed Hero Group, we are committed to revolutionizing the skill development landscape in India. Our programs, delivered by industry experts, are designed to empower professionals and students with the skills they need to thrive in today’s competitive job market.
Blogs
Reviews
Events
In the News
About Us
Contact us
Learning Hub
18003093939     ·     hello@herovired.com     ·    Whatsapp
Privacy policy and Terms of use

|

Sitemap

© 2024 Hero Vired. All rights reserved