Types of Join
Inner Join
Theta Join
EQUI join:
Natural Join (⋈)
Outer Join
Left Outer Join (A B)
Right Outer Join (A B)
Full Outer Join (A B)
Let’s see them in detail:
Inner Joins: Theta, Natural, EQUI Outer Join: Left, Right, Full
Inner Join further divided into three subtypes: An Inner join or equijoin is a comparator-based join which uses equality comparisons in the join-predicate. However, if you use other comparison operators like “>” it can’t be called equijoin.
Theta join Natural join EQUI join
Syntax: Theta join can use any conditions in the selection criteria. Consider the following tables. For example: For example: It performs selection forming equality on those attributes which appear in both relations and eliminates the duplicate attributes. Example: Consider the following two tables
Three types of Outer Joins are:
Left Outer Join Right Outer Join Full Outer Join
Consider the following 2 Tables In our example, let’s assume that you need to get the names of members and movies rented by them. Now we have a new member who has not rented any movie yet.
Example:
Summary:
There are mainly two types of joins in DBMS 1) Inner Join 2) Outer Join An inner join is the widely used join operation and can be considered as a default join-type. Inner Join is further divided into three subtypes: 1) Theta join 2) Natural join 3) EQUI join Theta Join allows you to merge two tables based on the condition represented by theta When a theta join uses only equivalence condition, it becomes an equi join. Natural join does not utilize any of the comparison operators. An outer join doesn’t require each record in the two join tables to have a matching record. Outer Join is further divided into three subtypes are: 1)Left Outer Join 2) Right Outer Join 3) Full Outer Join The LEFT Outer Join returns all the rows from the table on the left, even if no matching rows have been found in the table on the right. The RIGHT Outer Join returns all the columns from the table on the right, even if no matching rows have been found in the table on the left. In a full outer join, all tuples from both relations are included in the result, irrespective of the matching condition.
