Cross Joins

Logically, every join starts from the Cartesian product: each left row paired with each right row. A cross join returns it unfiltered, so 8 customers and 8 products make 64 rows. That suits grids of every combination: recommendations, report skeletons, test data.

SELECT cu.name, p.sku FROM customers AS cu CROSS JOIN products AS p
WHERE cu.country = 'MY' AND p.category_id = 3;
SELECT c.name, o.id
FROM customers AS c, orders AS o JOIN order_items AS i
  ON i.order_id = o.id AND o.customer_id = c.id;

+--------------+-----------+
| name         | sku       |
+--------------+-----------+
| Chen Wei     | BK-SQL-01 |
| Chen Wei     | BK-SQL-02 |
| Farah Rahman | BK-SQL-01 |
| Farah Rahman | BK-SQL-02 |
+--------------+-----------+
4 rows in set (0.000 sec)
ERROR 1054 (42S22): Unknown column 'c.id' in 'on clause'

Two tables of a million rows each cross-join into a trillion rows, so a forgotten join condition can stall a server. In MySQL 524 , JOIN, CROSS JOIN and INNER JOIN are syntactic equivalents, a product without ON and an inner join with it; standard SQL keeps them apart, so write CROSS JOIN only for the product. Don't mix commas with JOIN: JOIN binds tighter, MySQL reads c, (o JOIN i ON ...), and that ON cannot see c.