Q:

mysql linked list sort query, why is it working?

My table looks like this:
+------------------------+
| id | title  | position |
+------------------------+
|  1 | test 2 |        3 |
+------------------------+
|  2 | test 3 |        1 |
+------------------------+
|  3 | test 1 |        0 |
+------------------------+

I found this query which retrieves the rows ordered based on the position field which holds the id of the predecessor.
SELECT
    *
FROM
    mytable AS t1
LEFT JOIN
    mytable AS t2
ON t2.position = t1.id

I wonder why this is working because there is no order by clause and the database should't know that position 0 is the row to start at.

A:

The result is dependent on the order you inserted the rows into the table. If, for example, you had inserted the row with id=3 before you inserted the row with id=2, then you would have got a non-sorted result. 
As it stands, you are pulling the data out of t1 in the order of id because that is the order you put the elements into the table
See http://sqlfiddle.com/#!2/63a925/2 and try it for yourself.
N.B. Databases are not guaranteed to work as you state, it is simply that most databases work this way. You should not rely on this behaviour as a minor change to the schema or query could ruin your whole day! Note also that if id is a (primary?) key, the insert order will probably be overridden by the fact that the database will pull the rows out in the order of the index.

