Joins in DBMS

Challenge Inside! : Find out where you stand! Try quiz, solve problems & win rewards!
Video Tutorial
 Joins and Division Operators

Overview

Joins in Database Management System are used in relational algebra and SQL to join/combine more than one table to get some specific results out of those tables.

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.

IDNamePercentage %
1Rohan56
2Rohit85
3Amit75
4Ravi79
5Saiz65
6Tejan84
7Rishabh75

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

IDNameGenderSport
3AmitMCricket
23AmanMChess
5SaizMCricket
10ShreyaFBadminton
6TejanMChess
15SakshiFChess
2RohitMCricket
16TejanMChess
35ShubhiFCricket
1RohanMChess
4RaviMChess

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?

Note- The above table will be used as a reference to explain all the different types of Join further in this article.

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 :

IDNameGenderSport
3AmitMCricket
5SaizMCricket
2RohitMCricket

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

Types of Joins in DBMS

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:-

IDNamePercentageGenderSport
6Tejan84MChess
4Ravi79MChess

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:

IDNamePercentageGenderSport
6Tejan84MChess
1Rohan56MChess
4Ravi79MChess

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:

IDNamePercentageGenderSport
3Amit75MChess
5Saiz65MCricket
6Tejan84MChess
2Rohit85MCricket
1Rohan56MChess
4Ravi79MChess

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:

Boys.IDBoys.NameBoys.PercentageInterest.IDInterest.NameInterest.GenderInterest.Sport
1Rohan561RohanMChess
2Rohit851RohanMChess
3Amit751RohanMChess
4Ravi791RohanMChess
5Saiz651RohanMChess
6Tejan841RohanMChess
7Rishabh75NUllNULLNULLNULL

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:

Boys.IDBoys.NameBoys.PercentageInterest.IDInterest.NameInterest.GenderInterest.Sport
1Rohan561RohanMChess
2Rohit851RohanMChess
3Amit751RohanMChess
4Ravi791RohanMChess
5Saiz651RohanMChess
6Tejan841RohanMChess
NULLNULLNULL23AmanMChess
NULLNULLNULL10ShreyaFBadminton
NULLNULLNULL15SakshiFChess
NULLNULLNULL16TejanMChess
NULLNULLNULL35ShubhiFCricket

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:

Boys.IDBoys.NameBoys.PercentageInterest.IDInterest.NameInterest.GenderInterest.Sport
1Rohan561RohanMChess
2Rohit851RohanMChess
3Amit751RohanMChess
4Ravi791RohanMChess
5Saiz651RohanMChess
6Tejan841RohanMChess
7Rishabh75NUllNULLNULLNULL
NULLNULLNULL23AmanMChess
NULLNULLNULL10ShreyaFBadminton
NULLNULLNULL15SakshiFChess
NULLNULLNULL16TejanMChess
NULLNULLNULL35ShubhiFCricket

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.

Conclusion

  • Joins are used to Join two or more tables in the Database.
  • There are mainly three types of Join - Inner Join, Natural Join, Outer Join.
  • Inner joins are of two types - Theta Join and Equi Join.
  • Outer joins are of Three types - Left Outer Join, Right Outer Join and Full Outer Join.
  • Natural Join is performed only when there is at least one matching attribute in both the tables.
  • Left Outer join always returns all the rows of left table irrespective of the Join condition.
  • Right Outer Join always returns all the rows of right table irrespective of the Join condition.
  • Full Outer Join always returns all the Rows of both the table irrespective of the join condition.

Read More: