Join operation is one of the fundamental operations in Relational Algebra used to combine two or more relations based on a common attribute or set of attributes. The resulting relation contains all the attributes of the two relations combined. In this article, we will explore different types of join operations and their syntax.
Types of Join Operations:
- Inner Join:
Inner join is the most commonly used join operation, where only the matching tuples from both relations are combined. Inner join is represented by the symbol ⋈.
The syntax for Inner Join is:
R ⋈ S
Here, R and S are two relations to be joined, and ⋈ is the symbol for inner join.
Example:
Let’s consider two relations, CUSTOMER and ORDERS, which are to be joined based on the common attribute CustomerID.
CUSTOMER:
CustomerID Name Age Gender 100 Jessica 25 Female 101 John 30 Male 102 Michael 35 Male 103 Mary 28 Female 104 Robert 45 Male
ORDERS:
OrderID CustomerID OrderDate Amount 2001 100 01-01-2023 $200 2002 102 02-01-2023 $150 2003 103 03-01-2023 $300 2004 100 04-01-2023 $400 2005 105 05-01-2023 $250
Now, if we want to join these two relations based on the common attribute CustomerID, we can use inner join as follows:
CUSTOMER ⋈ ORDERS
The resulting relation will contain only the matching tuples from both relations where CustomerID is the same.
- Left Join:
Left join is a type of join where all the tuples from the left relation are included, and only the matching tuples from the right relation are combined. If there are no matching tuples in the right relation, then the resulting relation will have NULL values for the attributes of the right relation. Left join is represented by the symbol ⟕.
The syntax for Left Join is:
R ⟕ S
Here, R and S are two relations to be joined, and ⟕ is the symbol for left join.
Example:
Using the same relations, CUSTOMER and ORDERS, we can perform a left join as follows:
CUSTOMER ⟕ ORDERS
The resulting relation will contain all tuples from the CUSTOMER relation and only the matching tuples from the ORDERS relation. If there are no matching tuples in the ORDERS relation, then the attributes of the ORDERS relation will have NULL values.
- Right Join:
Right join is a type of join where all the tuples from the right relation are included, and only the matching tuples from the left relation are combined. If there are no matching tuples in the left relation, then the resulting relation will have NULL values for the attributes of the left relation. Right join is represented by the symbol ⟖.
The syntax for Right Join is:
R ⟖ S
Here, R and S are two relations to be joined, and ⟖ is the symbol for right join.
Example:
Using the same relations, CUSTOMER and ORDERS, we can perform a right join as follows:
CUSTOMER ⟖ ORDERS
The resulting relation will contain all tuples from the ORDERS relation and only the matching tuples from the CUSTOMER relation. If there are no matching tuples in the CUSTOMER relation, then the attributes of the CUSTOMER relation will have NULL values.
- Full Outer Join:
Full outer join is a type of join where all the tuples from both relations are included. If there are no matching tuples in either relation, then the resulting relation will have NULL values for the attributes of the non-matching relation. Full outer join is represented by the symbol ⟗.
The syntax for Full Outer Join is:
R ⟗ S
Here, R and S are two relations to be joined, and ⟗ is the symbol for full outer join.
Example:
Using the same relations, CUSTOMER and ORDERS, we can perform a full outer join as follows:
CUSTOMER ⟗ ORDERS
The resulting relation will contain all tuples from both the CUSTOMER and ORDERS relations. If there are no matching tuples in either relation, then the attributes of the non-matching relation will have NULL values.
- Natural Join:
Natural join is a type of join where the matching tuples from both relations are combined based on the common attribute(s). Natural join does not require specifying the common attribute(s), and it is performed automatically by the system. Natural join is represented by the symbol ⋉.
The syntax for Natural Join is:
R ⋉ S
Here, R and S are two relations to be joined, and ⋉ is the symbol for natural join.
Example:
Using the same relations, CUSTOMER and ORDERS, we can perform a natural join as follows:
CUSTOMER ⋉ ORDERS
The resulting relation will contain only the matching tuples from both relations where the attribute(s) are the same. In this case, the common attribute is CustomerID.
Conclusion:
Join operations are used to combine two or more relations based on a common attribute or set of attributes. The resulting relation contains all the attributes of the two relations combined. Inner join, left join, right join, full outer join, and natural join are the different types of join operations available in Relational Algebra. It is essential to understand the syntax and differences between these join operations to use them effectively in database management systems.
