SQL / Cross Join without clause Where / predicate using self join

advertisements

I have a simple table:

Cartesian
+--------+
| letter |
+--------+
| A      |
| B      |
| C      |
+--------+

Self Join query 1:

SELECT t1.letter, t2.letter
FROM cartesian t1, cartesian t2;

Result:

+--------+--------+
| letter | letter |
+--------+--------+
| A      | A      |
| B      | A      |
| C      | A      |
| A      | B      |
| B      | B      |
| C      | B      |
| A      | C      |
| B      | C      |
| C      | C      |
+--------+--------+

Self Join query 2:

SELECT t1.letter, t2.letter
FROM cartesian t2, cartesian t1;

Result:

+--------+--------+
| letter | letter |
+--------+--------+
| A      | A      |
| A      | B      |
| A      | C      |
| B      | A      |
| B      | B      |
| B      | C      |
| C      | A      |
| C      | B      |
| C      | C      |
+--------+--------+

Why are the results different? I am selecting the same columns and from the same tables. Only thing different is the ordering of tables in the FROM clause.


But the two results are not different at all if you disregard the ordering - the two sets contain the exact same pairs of tuples and therefore are exactly the same.

Any result that is returned and not explicitly ordered using an order by clause will not be guaranteed to be ordered in any way. The fact that the order does not appear to be random is due to the internal workings of the query processing component of the database and not something that can or should be relied on.

If you want order you have to state it.