limited time offer

What is CROSS JOIN in MySQL ?

The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN.This kind of result is called as Cartesian Product.
If WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.

Data of Cross Join can be used for testing purpose or several permutation and combination operations can be performed using Cross Join.

Syntax of MySQL Cross Join

SELECT * 
FROM table1 
CROSS JOIN table2;

Example of MySQL Cross Join

Product Table

+-----------+-----------------+--------------+
| ProductID | ProductName     | ProductPrice |
+-----------+-----------------+--------------+
|         3 | Farm House      |       200.00 |
|         4 | Peppy Panner    |       300.00 |
|         5 | Veggie Paradise |       250.00 |
+-----------+-----------------+--------------+

Sides Table

+---------+----------------------+------------+
| SidesID | SidesName            | SidesPrice |
+---------+----------------------+------------+
|       1 | Garlic Breadsticks   | 90.5       |
|       2 | Stuffed Garlic Bread | 110        |
|       3 | Cheese Dip           | 25.5       |
+---------+----------------------+------------+

MYSQL Cross Join Query 

use Shopping;
SELECT P.ProductName, S.SidesName,(P.ProductPrice+S.SidesPrice) AS TotalCost 
FROM Products P
cross join Sides S;

Output

+-----------------+----------------------+-----------+
| ProductName     | SidesName            | TotalCost |
+-----------------+----------------------+-----------+
| Farm House      | Garlic Breadsticks   |     290.5 |
| Peppy Panner    | Garlic Breadsticks   |     390.5 |
| Veggie Paradise | Garlic Breadsticks   |     340.5 |
| Farm House      | Stuffed Garlic Bread |       310 |
| Peppy Panner    | Stuffed Garlic Bread |       410 |
| Veggie Paradise | Stuffed Garlic Bread |       360 |
| Farm House      | Cheese Dip           |     225.5 |
| Peppy Panner    | Cheese Dip           |     325.5 |
| Veggie Paradise | Cheese Dip           |     275.5 |
+-----------------+----------------------+-----------+