Home

Learning Hub

Articles

Joins in DBMS – Different Types Explained with Examples

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:

Courses Table:

SQL Query:

Output:

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:

Departments Table:

SQL Query:

Output:

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:

Books Table:

SQL Query:

Output:

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

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:

Departments Table:

SQL Query:

Output:

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:

Orders Table:

SQL Query:

FROM Customers

LEFT JOIN Orders

ON Customers.customer_id = Orders.customer_id;

Output:

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:

Suppliers Table:

SQL Query:

FROM Products

RIGHT JOIN Suppliers

ON Products.product_id = Suppliers.product_id;

Output:

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.