Natural Join (Relational Algebra)

relational-algebra

Definition

Natural Join (Relational Algebra)

The natural join $R\bowtie S$ combines two relations via common attributes (same names and domains) by combining only tuples with the same values for common attributes.

Given two relations $R(A_1,\dots ,A_m,\underbrace{{{\mathbf{B}}_1,\dots {\mathbf{B}}_{\mathbf{k}}}_{}})$ and $S(\underbrace{{{\mathbf{B}}_1,\dots ,{\mathbf{B}}_{\mathbf{k}}}_{}},C_1,\dots ,C_n)$, the natural join can be expressed using projection, selection and the Cartesian Product:

$$R\bowtie S={\pi }_{A_1,\dots ,A_m,R.B_1,\dots ,R.B_k,C_1,\dots ,C_n}({\sigma }_{R.B_1=S.B_1\wedge \cdots \wedge R.B_k=S.B_k}(R\times S))$$

Example

Given two relations $students$ and $enrollments$:

Students Relation:

StudentID	Name	Major
S101	Alice	Physics
S102	Bob	CS
S103	Charlie	Biology

Enrolments Relation:

StudentID	CourseID	Grade
S101	C22	A
S102	C15	B
S102	C22	A
S104	C15	C

There’s only one common attribute, namely StudentID. Therefore, the join condition is:

$$\theta :=students.student\_id=enrolments.student\_id$$

Thus, the resulting relation $students\bowtie enrolments$ is:

StudentID	Name	Major	CourseID	Grade
S101	Alice	Physics	C22	A
S102	Bob	CS	C15	B
S102	Bob	CS	C22	A