Relational Operator - Equi-joins

1 - About

An Equi-join is a join where the condition (predicate) is an equality.

All other join operations with a predicate other than equality are called theta join

3 - Syntax

An equi-join links two relations (tables, …) on common values in a column in relation 1 with a column in relation 2.

3.1 - Conditional

The restriction conforms to the following syntax when the condition is specified:

  • Relational Syntax

<MATH> \LARGE R \bowtie_{R.A = S.A} S </MATH>

  • SQL

Table1.column_a = Table2.column_a

3.2 - Natural

A natural join is a shorthand equi-join where the equality applied to all shared attributes.

<MATH> \LARGE R \bowtie_{attrs(R) \cap attrs(S)} S </MATH> where:

  • <math>attrs(R) \cap attrs(S)</math> is the intersection of the attributes of R and S.

See SQL - Natural Join

4 - Rule

<MATH> \LARGE R_1 \bowtie_{ a = b } R_2 = \sigma_{a=b} ( R_1 \times R_2 ) </MATH>

The join of R1 and R2 on A=B is the same that the selection of the cross product of R1 and R2 where A=B.

5 - Notation

5.1 - Sql Notation

5.1.1 - Equality Notation


select *
from R1 JOIN R2
on R1.A = R2.A

5.1.2 - Cross product notation


select *
from R1, R2
where 
R1.A = R2.A


Data Science
Data Analysis
Statistics
Data Science
Linear Algebra Mathematics
Trigonometry

Powered by ComboStrap