Cross Joins

You saw in the previous lesson that if no relationship between joined tables is given in the WHERE clause, a Cartesian product or cross join is produced.

The ON keyword is not required to appear after the JOIN keyword, so the same result can be obtained using INNER JOIN and no ON clause.

However, for readability, you can use the CROSS JOIN keyword to show in your query that the Cartesian product is the desired result. The following example reproduces the cross join between sample tables t1 and t2 from the previous lesson:

MariaDB> SELECT *
 -> FROM t1
 -> CROSS JOIN t2;
+----+--------+----+--------+
| id | letter | id | letter |
+----+--------+----+--------+
| 1 | A | 1 | X |
| 2 | B | 1 | X |
| 3 | C | 1 | X |
| 1 | A | 2 | Y |
| 2 | B | 2 | Y |
| 3 | C | 2 | Y |
| 1 | A | 3 | Z |
| 2 | B | 3 | Z |
| 3 | C | 3 | Z |
+----+--------+----+--------+
9 rows in set (0.01 sec)

You will use a cross join very rarely, so it is helpful to use this syntax to make it clear that you actually want to perform this type of join.