MySQL CROSS JOIN

In the SQL language, the CROSS JOIN statement is a type of join on 2 SQL tables that allows returning the Cartesian product. In other words, it allows returning each row of a table with each row of another table. So performing the Cartesian product of table A that contains 30 results with a table B of 40 results will produce 1200 results (30 x 40 = 1200). In general, the CROSS JOIN statement is combined with the WHERE statement to filter the results that meet certain conditions.

 

 

 

Syntax:

To perform a join with CROSS JOIN, it is necessary to perform an SQL query respecting the following syntax:

SELECT *
FROM table1
CROSS JOIN table2

Alternative method for returning the same results:

SELECT *
FROM table1, table2

Either of these syntaxes allows associating all the results of table1 with each of the results of table2.
 

Example :

Let’s say we have a recipe application that contains 2 tables of ingredients, the vegetable table and the fruit table.
 
Vegetable table :

+----------+----------------+
|    vID   | vegetable_name |
+----------+----------------+
|     1    |   Carott       |
|     2    |   Onion        |
|     3    |   Leek         |
+----------+----------------+

 

 
Fruit table :

+----------+----------------+
|    fID   |   fruit_name   |
+----------+----------------+
|    80    |   Banana       |
|    81    |   Kiwi         |
|    82    |   Pear         |
+----------+----------------+

For some reason, the application must combine all vegetables with all fruits. All combinations must be displayed. To do this, one or more of the following queries must be performed:

SELECT vID, vegetable_name, fID, fruit_name
FROM vegetable
CROSS JOIN fruit

OR:

SELECT vID, vegetable_name, fID, fruit_name
FROM vegetable, fruit

Output:

+----------+----------------+-----------+---------------+
|    vID   | vegetable_name |     fID   |  fruit_name   |
+----------+----------------+-----------+---------------+
|     1    |    Carott      |     80    |    Banana     |
|     1    |    Carott      |     81    |    Kiwi       |
|     1    |    Carott      |     82    |    Pear       |
|     2    |    Onion       |     80    |    Banana     |
|     2    |    Onion       |     81    |    Kiwi       |
|     2    |    Onion       |     82    |    Pear       |
|     3    |    Leek        |     80    |    Banana     |
|     3    |    Leek        |     81    |    Kiwi       |
|     3    |    Leek        |     82    |    Pear       |
+----------+----------------+-----------+---------------+

The result shows that each vegetable is associated with each fruit. With 3 fruits and 3 vegetables, there are 9 rows of results (3 x 3 = 9).
 

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More