# 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.  

   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.  

   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.  

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