Joins in DBMS – Different Types Explained with Examples

Q: What is the primary purpose of a join operation in DBMS?

The primary purpose is to combine data from two or more tables based on related columns, enabling efficient data retrieval.

Q: What is the difference between inner join and outer join?

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.

Q: When should I use a theta join instead of an inner join?

Use a theta join when you need to join tables based on conditions other than equality, using comparison operators like >, =, etc.

Updated on July 27, 2024

Article Outline

Detailed Explanation of Inner Join in DBMS Comprehensive Overview of Theta Join in DBMS Equi Join in DBMS: Concepts and Examples Natural Join in DBMS Explained Understanding Outer Joins in DBMS Conclusion FAQs

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?

Get curriculum highlights, career paths, industry insights and accelerate your technology journey.

Download brochure

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.

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

What is the primary purpose of a join operation in DBMS?

The primary purpose is to combine data from two or more tables based on related columns, enabling efficient data retrieval.

What is the difference between inner join and outer join?

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.

When should I use a theta join instead of an inner join?

Use a theta join when you need to join tables based on conditions other than equality, using comparison operators like >, <, >=, etc.

How does a natural join differ from an equi join?

A natural join automatically matches columns with the same name and domain, while an equi join explicitly uses equality conditions to match specified columns.

Can I use multiple joins in a single query?

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.

Updated on July 27, 2024

Link

Programs tailored for your success

Popular

Management

Data Science

Finance

Technology

Future Tech

3 ASSURED INTERVIEWS

Accelerator Program in

Business Analytics and Data Science

With

Part-time · 10 months

Apply by : 21 December, 2024

Financial Analysis, Valuation, & Risk Management

3 ASSURED INTERVIEWS

Certificate Program in

Financial Analysis, Valuation, & Risk Management

With

Part-time · 7 months

Apply by : 21 December, 2024

INTERNSHIP ASSURANCE

Certificate Program in

DevOps & Cloud Engineering

With

Part-time · 7.5 months

Apply by : 21 December, 2024

Upskill with expert articles

View all

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.

Data Science

Accelerator Program in Business Analytics & Data Science

Integrated Program in Data Science, AI and ML

Accelerator Program in AI and Machine Learning

Advanced Certification Program in Data Science & Analytics

Certification Program in Data Analytics

Technology

Certificate Program in Full Stack Development with Specialization for Web and Mobile

Certificate Program in DevOps and Cloud Engineering

Certificate Program in Application Development

Certificate Program in Cybersecurity Essentials & Risk Assessment

Finance