Join is an additional or derived operator that simplifies queries but does not add any new power to basic relational algebra. Join is a combination of a Cartesian product followed by a selection process. Join = Cartesian Product + Selection
A Join operation pairs two tuples from different relations if and only if a given join condition is satisfied.
Symbol: ⨝
1.Inner Join (Join): Contains only those tuples that satisfy the matching condition.
2.Outer Join: Extension of join that contains matching tuples that satisfy the matching condition, along with some or all tuples that do not satisfy the matching condition. It contains all rows from either one or both relations.
⇒ It combines tuples from different relations provided they satisfy the theta (θ) condition.
⇒ It is a general case of join and is used when we want to join two or more relations based on some conditions.
⇒ The join condition is denoted by the symbol θ.
⇒ It uses all kinds of comparison operators like <, >, <=, >=, =, ≠.
Notation: A ⋈θ B
⇒ Where θ is a predicate/condition. It can use any comparison operator (<, >, <=, >=, =, ≠).
A ⋈θ B = σ θ(A×B)
⇒ When a theta join uses only equivalence (=) condition, it becomes an Equi join.
⇒ Equi join is a special case of theta (or conditional) join where the condition contains equalities (=).
⇒ A natural join can only be performed if there is at least one common attribute (column) that exists between two relations. In addition, the attributes must have the same name and domain.
⇒ A natural join does not use any comparison operator.
⇒ It is the same as an equi join which occurs implicitly by comparing all the common attributes (columns) in both relations, but the difference is that in a natural join, the common attributes appear only once. The resulting schema will change.
Notation: A⋈BA \bowtie BA⋈B
Note:
⇒ The natural join of two relations can be obtained by applying a projection operation to the equi join of two relations. In terms of basic operators:
⇒ Natural Join = Cartesian Product + Selection + Projection
⇒ Natural Join (⋈\bowtie⋈) is by default an inner join because the tuples that do not satisfy the join conditions do not appear in the result set.
⇒ Natural Join is very important.
Ex:
Ex:
An Inner join includes only those tuples with matching attributes, and the rest are discarded in the resulting relation. Therefore, we need to use outer joins to include all the remaining tuples from the participating relations in the resulting relation. The outer join operation is an extension of the join operation that avoids the loss of information.
Outer Join contains matching tuples that satisfy the matching condition, along with some or all tuples that do not satisfy the matching condition. It is based on both matched and unmatched tuples. It contains all rows from either one or both relations and uses NULL values. NULL signifies that the value is unknown or does not exist.
When applying join on two relations R₁ and R₂, some tuples of R₁ or R₂ do not appear in the result set if they do not satisfy the join conditions. However:
⇒ In Left outer join, all the tuples from the Left relation are included in the resulting relation. The tuples of R₁ that do not satisfy the join condition will have values as NULL for attributes of R₂.
⇒ In short:
⇒ All records from the left table
⇒ Only matching records from the right table
Symbol: ⟕
Notation: R₁ ⟕ R₂
Ex:
⇒ In Right outer join, all the tuples from the right relation R₂ are included in the resulting relation. The tuples of R₂ that do not satisfy the join condition will have values as NULL for attributes of R₁.
⇒ In short:
⇒ All records from the right table
⇒ Only matching records from the left table
Symbol: ⟖
Notation: R₁ ⟖ R₂
Ex:
⇒ In Full outer join, all the tuples from both the left relation R₁ and the right relation R₂ are included in the resulting relation. The tuples of both relations R₁ and R₂ that do not satisfy the join condition will have their respective unmatched attributes set to NULL.
⇒ In short:
⇒ All records from both tables
Symbol:⟗
Notation: R₁ ⟗ R₂
Ex:
