Home >>DBMS Tutorial >DBMS Database Joins

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

SID Name Std
101 AB 10
102 BC 11

Student

Class Subject
10 Math
10 English
11 Music
11 Sports

Student_Detail −

STUDENT ⋈Student.Std = Subject.Class SUBJECT

Student detail

SID Name Std Class Subject
101 AB 10 10 Math
101 AB 10 10 English
102 BC 11 11 Music
102 BC 11 11 Sports

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

CID Course Dept
CS01 Database CS
ME01 Mechanics ME
EE01 Electronics EE

HoD

Dept Head
CS AB
ME X
EE Y

Courses ⋈ HoD

Dept CID Course Head
CS CS01 Database AB
ME ME01 Mechanics X
EE EE01 Electronics Y

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

A B
100 Database
101 Mechanics
102 Electronics

Right

A B
100 AB
102 X
104 Y

Courses HoD

A B C D
100 Database 100 AB
102 Mechanics --- ---
102 Electronics 102 Y

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

A B C D
100 Database 100 AB
102 Electronics 102 X
--- --- 104 Y

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

A B C D
100 Database 100 AB
101 Mechanics --- ---
102 Electronics 102 X
--- --- 104 Y