Join

Definition

The join is a binary operator of the relational algebra that combines tuples of different relations based on a relationship between values of their attributes. The primitive version of the join operator is called natural join. Given two relation instances R₁, over set of attributes U₁, and R₂ over set of attributes U₂, the natural join R₁ ⋈ R₂ returns a new relation, over set of attributes U₁ ∪ U₂, consisting of tuples {t |t(U₁) ∈ R₁ and t(U₂) ∈ R₂}. Here t(U) denotes the restriction of the tuple t to attributes in the set U.

A derivable version of the join operator is obtained by composing the natural join with the selection operator σ: the theta join R₁ ⋈_θR₂ is defined as σ_θ (R₁ ⋈ R₂), where θ is an arbitrary condition allowed in a generalized selection over set of attributes U₁ ∪ U₂. In the case that θ is a conjunction of equality atoms of the form A = B, where A is an attribute in U₁ and B an attribute in U₂, the theta join is called equijoin.

Another derivable join...