Search code examples
mysqlcartesian-productcross-join

SQL Cartesian Product / Cross Join direction

I have two simple tables for this subject:

   abc       numbers
+--------+ +--------+
| letter | | number |
+--------+ +--------+
| a      | |      1 |
| b      | |      2 |
+--------+ |      3 |
           +--------+

Query:

SELECT letter, number
FROM abc
CROSS JOIN numbers;

Result:

+--------+--------+
| letter | number |
+--------+--------+
| a      |      1 |
| b      |      1 |
| a      |      2 |
| b      |      2 |
| a      |      3 |
| b      |      3 |
+--------+--------+

Expected result:

+--------+--------+
| letter | number |
+--------+--------+
| a      |      1 |
| a      |      2 |
| a      |      3 |
| b      |      1 |
| b      |      2 |
| b      |      3 |
+--------+--------+

Why did it not come out as I expected?

From what I have read about cartesian products in this article it should've come out as I expected.

enter image description here

Can anyone explain step by step how the result I got was processed by MySql?

Solution

  • The result sets are the same. SQL result sets have no ordering, unless you specify an order by. Both contain the same rows, so they are the same.

    If you expect a query to return rows in a particular order and you do not use an order by, then your expectation is simply wrong.