JOIN (Relational Operation)

We can simplify the sequence of CARTESIAN PRODUCT followed by SELECT into a single operation JOIN.

The general form of a join operation on two relations $R(A_1,A_2,\dots ,A_n)$ and $S(B_1,B_2,\dots ,B_m)$ is:

$$R{\bowtie }_{<\text{join condition}>}S$$

Where $R$ and $S$ can be any relations that result from general relational algebra expressions.

Examples:

• Suppose we want to retrieve the name of the manager of each department. $$DEPT\_MGR\leftarrow DEPARTMENT{\bowtie }_{MGRSSN=SSN}EMPLOYEE$$ The join condition is MGRSSN=SSN or DEPT.MGRSSN = EMPLOYEE.SSN.

Theta JOIN

Theta JOIN (Relational Operation)

The general case of the JOIN operation is called a Theta-join:

$$R\underset{\text{theta}}{\bowtie }S$$

The join condition is called theta. Theta can be any general boolean expression on the attributes of $R$ and $S$, e.g.:

$$R.Ai<S.Bj\text{ and }(R.Ak=S.Bi\text{ or }R.Ap<S.Bq)$$Link to original

EQUIJOIN

EQUIJOIN (Relational Operation)

The most common use of join involves join conditions with equality comparisons only, in this case, it is called an EQUIJOIN.

In the result of an EQUIJOIN, we have one or more pairs of attributes that have identical values in the tuple.

$$DEPT\_MGR\leftarrow DEPARTMENT{\bowtie }_{MGRSSN=SSN}EMPLOYEE$$Link to original

NATURAL JOIN

NATURAL JOIN (Relational Operation)

The NATURAL JOIN operation, denoted by ${}^{\ast }$, was created to get rid of the second attribute in an EQUIJOIN condition.

To apply aw natural join on the DNUMBER attributes of DEPARTMENT and DEPT_LOCATIONS, it is sufficient to write:

$$\text{DEPT\_LOCS}\leftarrow \text{DEPARTMENT }{}^{\ast }\text{ DEPT\_LOCATIONS}$$Link to original