Join in DBMS is a Boolean operation which allows us to combine join product and selection in one single statement. The goal of creating a join condition is that it helps us to combine the data from two or more DBMS tables.
Join is a combination of a Cartesian product followed by a selection process. A Join operation pairs two tuples from different relations, if and only if a given join condition is satisfied.
#Types of Joins
There are mainly two sorts of joins in DBMS:
- Inner Joins: Theta Join, Natural Join, EQUI Join
- Outer Join: Left Join, Right Join, Full Join
Let’s go forwards in detail:
1. Inner Join :
INNER JOIN is used to return rows from both tables which satisfy the given condition. It is the foremost widely used join operation and can be considered as a default join-type.
An Inner join or equijoin is a comparator-based join that uses equality comparisons within the join-predicate.
Inner Join further divided into three subtypes:
- Theta join
- Natural join
- EQUI join
Theta Join combines tuples from different relations provided they satisfy the θ condition. The join condition is denoted by the symbol θ. The general case of JOIN operation is named as a Theta join.
R1 ⋈θ R2
R1 and R2 are relations having attributes (A1, A2, .., An) and (B1, B2,.. , Bn) such that the attributes do not have anything in common, that is R1 ∩ R2 = Φ.
Theta join can use all kinds of comparison operators.
–NATURAL JOIN (⋈)
Natural Join does not utilize any of the comparison operators. In this nature of join, the attributes should have the same name and domain. In Natural Join, there should be at least one common attribute between two relations.
It performs selection forming equality on those attributes which appear in both relations and eliminates the duplicate attributes.
R1 ⋈ R2
– EQUI JOIN
Equi Join is done when a Theta join uses only the equivalence condition. EQUI join is the most difficult operation to implement efficiently in an RDBMS, and one reason why RDBMS have essential performance problems.
2. Outer Join:
An OUTER JOIN does not require each record within the two join tables to have a possess an identical record. In this type of join, the table retains each record even if no other matching record exists.
Three types of Outer Joins are:
- Left Outer Join
- Right Outer Join
- Full Outer Join
– Left Outer Join:
LEFT JOIN returns all the rows from the table on the left even if no matching rows are found in the table on the right. When no matching record found in the table on the right, NULL is returned.
-Right Outer Join:
All the tuples from the Right relation, B, are included in the resulting relation. If there are tuples in S without any matching tuple in R, then the R-attributes of resulting relation are made NULL.
–Full Outer Join:
All the tuples from both participating relations are included in the resulting relation. If there are no matching tuples for both relations, their respective unmatched attributes are made NULL.
- Mainly two sorts of joins are available in DBMS
1) Inner Join
2) Outer Join
- In DBMS default join-type is inner Join and it is widely used join operation.
- Inner Join has divided into three subtypes: 1) Theta join 2) Natural join 3) EQUI join
- Theta Join allows us to combined two tables based on the condition. Theta Join represented by theta
- When a theta join uses only equivalence condition, it becomes an equijoin.
- Natural join does not utilize any comparison operator.
- An outer join doesn’t require each record within the two join tables to possess an identical record.
- Outer Join is divided into three subtypes are: 1) Left Outer Join 2) Right Outer Join 3) Full Outer Join
I hope this post helps you to understand all the joins in Database Management System which helps you to combine the data from two or more tables.
Keep learning 🙂