Joins in DBMS

Introduction

Let's understand join in relational algebra with the help of an intuitive example :

Let there be a database of all the class 12th boys students in a school. The table name is Boys.

Let there be a database of all students in the school who are interested in different sports. The table name is Interest.

Now, if someone asks me to find the data of all the 12th class boys in the school who are interested in Cricket. How would I proceed?

You would say just filter out all the boys students from the Interest table. But that would not work as there can be student of same name in different class with different IDs.

As in the Boys table above, there is a boy named Tejan in class 12th and also there is a boy named Tejan with ID=16 in the Interest Table but they are not same. So the filtering method can lead to errors.

We can find the solution by joining both the tables and then filtering results based on some conditions. Let's see how?

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.