Joins in DBMS

This article delves into the pivotal concept of 'join' in Database Management Systems (DBMS). A 'join' merges rows from multiple tables based on related columns, facilitating simultaneous data retrieval from interconnected tables. Exploring SQL's JOIN clause, readers will grasp its syntax, various types, practical examples, and frequently asked questions. Essentially, JOIN in SQL encapsulates the act of amalgamating tables, enhancing data querying capabilities across databases.

What is Join in DBMS?

Joins in relational algebra are simply cartesian products followed by selection.

In the above example, if we combine both the Boys table and Interest table such that the ID of students in the Boys table is same as the IDs of students in Interest table, then it will be easy for us to filter out the desired result of all the boys student of class 12th who are intrested in Cricket.

If we perform Inner Join on both tables with one condition as :

Boys ⋈(Boys.ID = Interest.ID and Interest.Sport=Cricket) Interest

The join condition (Boys.ID = Interest.ID and Interest.Sport=Cricket) first performs Cartesian product on both tables and then makes selection to give only those class 12th boys who are interested in Cricket.

The Result of the above Relational algebra query will be :

Types of Joins

There can be more than one way to join the database tables. So different types of Joins are:-

• Inner Join
• Natural Join
• Outer Join

Inner Join

It selects the values present in both the Table performing Inner join.

• Inner Join is further classified into
  • Theta Join
  • Equi Join

Theta Join

Theta Join is used to join two tables based on some conditions. The condition can be on any attributes of the tables performing Theta join. Any comparison operator can be used in the condition.

A ⋈θ B where θ is the condition for join.

Let's understand Theta Join with the Boys and Interest tables used above :

What if we want to find all the boys student in class 12th who like chess and have percentage greater than 70%. How can we find it out with the help of Theta join?

Theta Join - Boys ⋈(Boys.ID = Interest.ID and Interest.Sport = Chess and Boys.Percentage > 70 ) Interest So the condition here is Boys.ID = Interest.ID and Interest.Sport = Chess , so while performing join, we will have to check this condition every time two rows are joined.

The result of Theta Join will be:-

Equi Join

Equi join is same as Theta Join, but the only condition is it only uses equivalence condition while performing join between two tables.

A ⋈(... = ...) B, where (... = ... ) is the equivalence condition on any of the attributes of the joining table.

In the above example, what if we are told to find out all the students of class 12th who have interest in chess only?

We can perform Equi join as :

Equi join: Boys ⋈(Boys.ID = Interset.ID and Interest.Sport = Chess) Interest

Result after performing Equi join:

Natural Join

Natural join is also considered a type of inner join but it does not use any comparison operator for join condition. It joins the table only when the two tables have at least one common attribute with same name and domain.

In the result of the Natural Join the common attribute only appears once.

It will be more clear with help of an example :

What if we are told to find all the Students of class 12th and their sports interest we can apply Natural Join as :

Natural Join: Boys ⋈ Interest

So when we perform Natural Join on table Boys and table Interest they both have a common attribute ID and have the same domain.

So, the Result of Natural Join will be:

In the table the common attribute ID is only displayed once in the result.

Outer Join

Outer Join in Relational algebra returns all the attributes of both the table depending on the condition. If some attribute value is not present for any one of the tables it returns NULL in the respective row of the table attribute.

• It is further classified as:
  • Left Outer Join
  • Right Outer Join
  • Full Outer Join

Let's see how these Joins are performed.

Left Outer Join

It returns all the rows of the left table even if there is no matching row for it in the right table performing Left Outer Join.

A Left Outer Join B

Let's perform Left Outer Join on table Boys and Interest and find out all the boys of class 12th and their sports interest.

If we perform Left Outer Join on table Boys and table Interest such that Boys.ID = Interest.ID . Then Result of the Join will be:

Clearly, we can observe that all the rows of the left table, i.e., table Boys is present in the result.

Right Outer Join

It returns all the rows of the second table even if there is no matching row for it in the first table performing Right Outer Join.

ARight Outer Join B

Let's perform Right Outer Join on table Boys and Interest and find out all the boys of class 12th and their sports interest.

If we perform Right Outer Join on table Boys and table Interest such that Boys.ID = Interest.ID . Then Result of the join will be:

Clearly, we can observe that all the rows of the right table, i.e., table Interest is present in the result.

Full Outer Join

It returns all the rows of the first and second Table.

A Full Outer Join B

Let's perform Full Outer Join on table Boys and Interest and find out all the boys of class 12th and their sports interest.

If we perform Full Outer Join on Table Boys and Table Interest such that Boys.ID = Interest.ID . Then the result of the join will be:

Clearly, we can observe that all the rows of the right table and left Table, i.e., Table B and A are present in the result.

Read More:

• Keys is DBMS.
• Relational Calculus in DBMS.
• Relational Algebra in DBMS.