# Joins Revisited

* Join operations take two relations and return as a result another relation
* A join operation is a Cartesian product which requires that tuples in the two relations match (under some condition). It also specifies the attributes that are present in the result of the join
* The join operations are typically used as subquery expressions in the _from_ clause
* **Join condition** – defines which tuples in the two relations match, and what attributes are present in the result of the join
* **Join type** – defines how tuples in each relation that do not match any tuple in the other relation (based on the join condition) are treated

## ![](https://1736932896-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M5-0RGXrGEYT2sPemdS%2F-M5-0Rgnjb23s9oFr3lT%2F-M5-0_QjVze3qn3fNAgc%2FJOINS.png?generation=1586990836043770\&alt=media)Types of Joins

The cartesian product of two relations ($$r\times s$$) will return all possible combinations of the tuples in r and s.

The cartesian product is the first step in joining two relations. A join is simply a catresian product followed by selection/projection.

We have 3 kinds of joins:

1.  **Conditional Join**

    This is the same as performing a cartesian product based on a condition. &#x20;

    It is denoted by ($$r \bowtie _C s$$) and is equivalent to $$\sigma_C (r\times s)$$.
2.  **Equijoin**

    The result of this join will only contain records having equal values for the common field. &#x20;

    Ex. $$r \bowtie _{r.id=s.id} s$$
3.  **Natural Join**

    This is an equijoin considering all fields common between r and s. These fields do not have to be mentioned in a condition. &#x20;

    If there are no common fields, natural join will be the same as a cross product.
