DBMS Database Joins

DBMS Database Joins

We understand the advantages of taking a two-relations. Cartesian product, which gives us all the possible tuples that are paired together. In some instances, however, it might not be feasible for us to take a Cartesian product where we experience enormous relations with thousands of tuples with a considerable number of attributes.

Join is a combination, followed by a selection process, of a Cartesian product. If and only if a given join condition is fulfilled, a join operation pairs two tuples from different relations.

In the following sections, we will briefly define different types of joint.

Theta (θ) Join

Theta join combines tuples from different relations if they satisfy the condition of the theta. The join condition is indicated by the symbol 0.

Notation R1 ⋈θ R2

R1 and R2 are relations having attributes (A1, A2, .., An) and (B1, B2,.. ,Bn) such that the attributes don’t have anything in common, that is R1 ∩ R2 = Φ.

Theta join can use all kinds of comparison operators.

Student

Student

Student_Detail −

STUDENT ⋈Student.Std = Subject.Class SUBJECT

Student detail

Equijoin

If Theta join uses only the operator of equality comparison, it is said to be equijoin. Equijoin refers to the above case.

Natural Join (⋈)

No comparison operator is used by Natural Join. The way a Cartesian product does, it doesn't concatenate. Only if there is at least one common attribute that exists between two relations can we perform a Natural Join. Furthermore, the attributes have to have the same domain and name.

Natural join acts on all matching attributes in which the attribute values are the same in both relations.

Courses

HoD

Courses ⋈ HoD

Outer Joins

They are called inner joins, Theta Join, Equijoin, and Natural Join. Only those tuples with matching attributes are included in an inner join and the rest are discarded in the resulting relation. Therefore, to include all the tuples from the participating ties in the resulting relation, we need to use outer joins. Three types of outer joins are available: left outer join, right outer join, and full outer join.

Left Outer Join(R S)

In the resulting relation, all tuples from the Left Relation, R, are included. In the resulting relation S, if there are tuples in R without any matching tuple, then the S-attributes of the resulting relation are rendered NULL.

Left

Right

Courses HoD

Right Outer Join: ( R S )

The resulting relation contains all the tuples from the Right Relation, S. The R-attributes of the resulting relation are rendered NULL if there are tuples in S without any matching tuple in R.

Courses HoD

Full Outer Join: ( R S)

All the tuples are used in the resulting relation from all participating relations. If both relations have no matching tuples, their respective unmatched attributes are made NULL.

Courses HoD