Joins in DBMS – Different Types Explained with Examples

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

Joins in DBMS are a solution since they allow for combining 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 be extremely difficult to retrieve data from more than one table if no joins exist.

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

What is join in DBMS

A DBMS JOIN operates through SQL to link different tables using matching field contents. The DBMS JOIN functionality allows users to seek related data by creating linkages between different tables which helps produce efficient data retrieval and organization processes. Hard and soft JOIN operations including INNER JOIN and LEFT JOIN and FULL JOIN enable diverse ways to conduct data unions and analysis.

In a database management system (DBMS), a join operation is used to combine rows from two or more tables in a related column. Joins help retrieve meaningful data by establishing a connection between tables. There are several types of DBMS joins.

• INNER JOIN: This is used to perform the returns only for the matching rows from both tables.
• Outer JOIN: These outer joins include unmatched rows as well. There are three types.
• LEFT JOIN (LEFT OUTER JOIN): This returns all rows from the left table and the matched rows from the right table. If no match is found, NULL values are returned for columns from the right table.
• Right Join(Right Outer Join): This returns all rows from the right table and the matched rows from the left table. If no match is found, NULL values are returned for columns from the left table.
• FULL JOIN(FULL OUTER JOIN): This function returns all rows from both tables. If there is not match found, NULL values appear for missing columns from either
• CROSS JOIN: This produces the cartesian prodcut of both tables. Each row from the first table is combined with every row from the second table.
• SELF JOIN: This table join with itself. It is very useful when comparing rows within the same table.
• Natural JOIN: It is automatically joins table based on columns with the same name and data type. No need to specify the join condition explicitly.

Detailed Explanation of Inner Join in DBMS

Inner join is one of the most commonly used DBMS join. 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 enrolled in courses. Students without courses are not included.

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 !=.

Here are the dbms joins with examples

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.

Here are the joins in dbms with examples

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.

Natural Join in DBMS Explained

Have you ever wondered how to 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.

Here is the joins in dbms with examples

Also Read: DBMS Interview Questions With Answers

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:

Output:

In this join with dbms example, 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.

Also Read: Relational Algebra in DBMS 

Example:

Consider two tables, Products and Suppliers:

Products Table:

Suppliers Table:

SQL Query:

Output:

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

Conclusion

Data management operations through DBMS become efficient because the technology allows users to store and retrieve data while managing its manipulation. Database management systems play multiple essential functions to ensure data consistency and protection while providing convenient accessibility, making it practical for organisations of different scales. Background joins and other types available through DBMS enable users to discover significant relationships between their data across different tables, which leads to improved decision processes.

For those eager to build dynamic web and mobile applications while mastering database management, explore the Full Stack Development with Specialization for Web and Mobile  powered by Hero Vired and take your tech skills to the next level!