What is Join in DBMS and what are its types?

In practical scenarios, whenever we make use of DBMS, we deal with multiple database tables. Actually, in most cases, we need to combinedly work on these tables. We have to use the combined result of these tables for more operations.

These operations can be achieved in DBMS using the ‘JOIN’ clause. So in this blog, we will learn what is a join clause and the various types of the join along with examples. We will take all the examples in this blog using the MySQL database only.

NOTE: This is one of the important topics that are generally asked in the interviews of the any company. So, learn with ease.

Join

In DBMS, a join statement is mainly used to combine two tables based on a specified common field between them. If we talk in terms of Relational algebra, it is the cartesian product of two tables followed by the selection operation. Thus, we can execute the product and selection process on two tables using a single join statement. We can use either 'on' or 'using' clause in MySQL to apply predicates to the join queries.

A Join can be broadly divided into two types:

1. Inner Join
2. Outer Join

For all the examples, we will consider the below-mentioned employee and department table.

Now we will learn about each of these two joins in detail.

Inner Join

Inner Join is a join that can be used to return all the values that have matching values in both the tables. Inner Join can be depicted using the below diagram.

The inner join can be further divided into the following types:

1. Equi Join
2. Natural Join

Now let us learn about these inner joins one-by-one.

1. Equi Join

Equi Join is an inner join that uses the equivalence condition for fetching the values of two tables.

The MySQL query for equi join can be as follows:

Select employee.empId, employee.empName, department.deptName from employee Inner Join department on employee.deptId = department.deptId;

The returned values for the above query is as follows:

2. Natural Join

Natural Join is an inner join that returns the values of the two tables on the basis of a common attribute that has the same name and domain. It does not use any comparison operator. It also removes the duplicate attribute from the results.

The MySQL query for natural join can be as follows:

Select * from employee Natural Join department;

The above query will return the values of tables removing the duplicates. If we want to specify the attribute names, the query will be as follows:

Select employee.empId, employee.empName, department.deptId, department.deptName from employee Natural Join department;

The returned values for the above two queries are as follows:

Outer Join

Outer Join is a join that can be used to return the records in both the tables whether it has matching records in both the tables or not.

The outer join can be further divided into three types:

1. Left-Outer Join
2. Right-Outer Join
3. Full-Outer Join

we'll learn about these outer joins one-by-one.

1. Left-Outer Join:

The Left-Outer Join is an outer join that returns all the values of the left table, and the values of the right table that has matching values in the left table. If there is no matching result in the right table, it will return null values in that field. The Left-Outer Join can be depicted using the below diagram.

The MySQL query for left-outer join can be as follows:

Select employee.empId, employee.empName, department.deptName from employee Left Outer Join department on employee.deptId = department.deptId;

The returned values for the above query is as follows:

2. Right-Outer Join:

The Right-Outer Join is an outer join that returns all the values of the right table, and the values of the left table that has matching values in the right table. The Right-Outer Join can be depicted using the below diagram.

The MySQL query for right-outer join can be as follows:

Select employee.empId, employee.empName, department.deptName from employee Right Outer Join department on employee.deptId = department.deptId;

The returned values for the above query is as follows:

3. Full-Outer Join:

The Full-Outer join contains all the values of both the tables whether they have matching values in them or not. The Full-Outer Join can be depicted using the below diagram.

The MySQL query for full-outer join can be as follows:

Select * from employee Full Join department;

The returned values for the above query is as follows:

This is all about join in DBMS. Hope you learned something new today. That's it for this blog.

Do share this blog with your friends to spread the knowledge. Visit our YouTube channel for more content. You can read more blogs from here .

Keep Learning :)

Team AfterAcademy!