Joining tables

While examining the GROUP BY clause, we joined two tables into one resultset. This is a common operation when working with relational databases, because a table is generally correlated with other tables. The statement that allows us to do so is JOIN.

There are several types of joins:

• CROSS JOIN

• INNER JOIN

• LEFT JOIN

• RIGHT JOIN

The cross join operation

A CROSS JOIN is an operation that associates all the rows from the left table to all the rows in the right table. The number of rows in the resultset is the product of the number of rows in the left table multiplied by the number of rows in the second table, which is usually a huge number. This operation is very rarely used, and it is very expensive.

The following is an example of CROSS JOIN. Imagine we have two tables, both containing the numbers from 1 to 3:

MariaDB [test]> SELECT a.c, b.c FROM t1 a CROSS JOIN t2 b;
+------+------+
| c    | c    |
+------+------+
|    1 |    1 |
|    2 |    1 |
|    3 |    1 |
|    1 |  ...