DBMS Joins - MetaTutorials

MetaTutorials

The Tutor is Yours!

DBMS Joins

Here, we will learn about the Joins of the database management system in detail with examples. 
DBMS Joins combine rows or tuples of two or more database tables based on their common fields or columns. In SQL, join operation can be achieved between the table by using the JOIN clause. The database tables can only be combined when the specific join condition is satisfied. 

Following are the different types of Joins in database management system:
  1. Inner Join
  2. Outer Join
  3. Natural Join
  4. Self-Join

Inner Join

DBMS Inner Join displays all the values which are common in both the tables. This join is also called Equi-Join. It is considered as the default join. And, it is most widely used. 
When the specified condition is satisfied, then this operation displays the combination of all the rows from both the selected tables in the resultant table. This join discards all the rows which do not fulfill the specified condition. 
Syntax
Relation1.Column_Name = Relation2.Column_Name

Example
This example uses the following tables whose names are Employee and Department. The Employee table has five columns and the Department table has two columns. 
This join operation displays those employees who works in department. 

Outer Join

DBMS Outer Join displays both common and uncommon values from the selected tables. This join fulfills the limitation of inner join which does not show the unmatched values. This concept is basically used for displaying those tuples which are not common in both tables. 

Following are the three different types of outer join in DBMS:
  1. Left Outer Join
  2. Right Outer Join
  3. Full Outer Join

Left Outer Join

Left Outer Join displays all the rows of the left table and the matching rows from the right table. This type of join is also known as Left Join. 
If there is no match of any tuple with the right table, then the NULL value will be displayed at that tuple in the resulting table. This join is indicated by the ⟕.
Syntax
Relation1 ⟕ Relation2

Example
This example uses the following tables whose names are Employee and Department. The Employee table has five columns and the Department table has two columns. 

Right Outer Join

Right Outer Join displays all the rows of the right table and the matching rows from the left table. This type of join is also known as Right Join. It is opposite of the left outer join. 
If there is no match of any tuple with the left table, then the NULL value will be displayed at that tuple in the resulting table. This join is indicated by the ⟖.
Syntax
Relation1  Relation2

Example
This example uses the following tables whose names are Employee and Department. The Employee table has five columns and the Department table has two columns. 

Full Outer Join

Full Outer Join displays all the rows of both tables. This join is indicated by the ⟗. This join shows the combination of left and right outer join in the result.
Syntax
Relation1  Relation2

Example
This example uses the following tables whose names are Employee and Department. The Employee table has five columns and the Department table has two columns. 

Natural Join

DBMS Natural Join displays all the values of both the table if there is at least one common attribute in both the table.  It is indicated by ⋈. 
It is similar to the Inner (Equi-join), but the only difference is that it does not require to specify any condition. 

Syntax
Relation1  Relation2

Example
This example uses the following tables whose names are Employee and Department. The Employee table has five columns and the Department table has two columns. 

Self-Join

DBMS Self-Join combines the table to itself. In this type of join, each row is combined to itself and the other rows or tuples of the same table. 
Self-Join means the combination of two copies of the same table. 

Syntax
Relation_Alias1.Column_Name = Relation_Alias2.Column_Name

Example
This example uses the following single table whose name is Student. The Student table has five columns.
In this example, this operation displays those students who play a Football game.