JOIN -Operations to Join Two Tables

This section describes JOIN operations to join two tables in different ways: CROSS JOIN, INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN.

A join table is the output table of a join operation on two tables. There are several types of join operations:

1. Cross Join - Takes each row in the left table, and joins onto all rows in the right table. The number of columns of the output table is the number of columns of the left table plus the number of columns of the right table. The number of rows of the output table is the number of rows of the left table times the number of rows of the right table. A cross join operation is also called Cartesian product operation. There are two ways to write a cross join table:

```table_l, table_r
table_l CROSS JOIN table_r
```

The cross join operation logic can be expressed as:

```Loop on each row in the left table (L)
Loop on each row in the right table (R)
Generate an output row with all columns of the current row of L
and all columns of the current row of R
End of loop
End of loop
```

2. Inner Join - Takes the output table of the cross join operation, and filter out rows that do not satisfy the specified join condition, which should be an equality comparison of one column in the left table and one column in the right table. The syntax form of an inner join table is:

```table_l INNER JOIN table_r ON table_l.column_l=table_r.column_r
```

The inner join operation logic can be expressed as:

```Loop on each row in the left table (L)
Loop on each row in the right table (R)
If the value of column_l equals to the value of column_r
Generate an output row with all columns of the current row
of L and all columns of the current row of R
Break the loop on R
End if
End of loop
End of loop
```

3. Left Outer Join - Takes the output table of the inner join operation, and adds one row for each row in the left table that has no matching rows in the right table. This new output row will contain the row from the left table and null values to occupy the output columns corresponding to the right table. The syntax form of a left outer join table is:

```table_l LEFT OUTER JOIN table_r ON table_l.column_l=table_r.column_r
```

The left outer join operation logic can be expressed as:

```Loop on each row in the left table (L)
Set match_found = FALSE
Loop on each row in the right table (R)
If the value of column_l equals to the value of column_r
Generate an output row with all columns of the current row
of L and all columns of the current row of R
Set match_found = TRUE
End if
End of loop
If match_found is FALSE
Generate an output row with all columns of the current row of L
and null values for columns corresponding to columns of R
End if
End of loop
```

4. Right Outer Join - Takes the output table of the inner join operation, and adds one row for each row in the right table that has no matching rows in the left table. This new output row will contain the row from the right table and null values to occupy the output columns corresponding to the left table. The syntax form of a right outer join table is:

```table_l RIGHT OUTER JOIN table_r ON table_l.column_l=table_r.column_r
```

The right outer join operation logic can be expressed as:

```Loop on each row in the right table (R)
Set match_found = FALSE
Loop on each row in the left table (L)
If the value of column_l equals to the value of column_r
Generate an output row with all columns of the current row
of L and all columns of the current row of R
Set match_found = TRUE
End if
End of loop
If match_found is FALSE
Generate an output row with all columns of the current row of R
and null values for columns corresponding to columns of L
End if
End of loop
```

Last update: 2015.