Join Query effects natural ordering [closed]

Question

If we have 2 tables

**user**
+----+------+------+-----------+
| id | user | pass | gender_id |
+----+------+------+-----------+
| 1  | lisa | good |     11    |
| 2  | jack | pass |     10    |
| 3  | paul | 5555 |     10    |
| 4  | rich | none |     10    |
+----+------+------+-----------+

**gender**
+----+--------+
| id | gender |
+----+--------+
| 11 | female |
| 10 | male   |
+----+--------+

If we join them like so

SELECT user.id, user.user, user.pass, user.gender_id, gender.gender FROM `user` JOIN `gender` on user.gender_id

The result of query

array(4) {
  [0]=>
  array(4) {
    ["id"]=>
    string(3) "2"
    ["type"]=>
    string(6) "jack"
    ["pass"]=>
    string(11) "pass"
    ["gender"]=>
    string(1) "male"
    }
  [1]=>
  array(4) {
    ["id"]=>
    string(3) "3"
    ["type"]=>
    string(6) "paul"
    ["pass"]=>
    string(11) "5555"
    ["gender"]=>
    string(1) "male"
    }
  [2]=>
  array(4) {
    ["id"]=>
    string(3) "4"
    ["type"]=>
    string(6) "rich"
    ["pass"]=>
    string(11) "none"
    ["gender"]=>
    string(1) "male"
    }
  [3]=>
  array(4) {
    ["id"]=>
    string(3) "1"
    ["type"]=>
    string(6) "lisa"
    ["pass"]=>
    string(11) "good"
    ["gender"]=>
    string(1) "female"
    }

The order has now changed even though we did not specify this.(user lisa is last) If we change lisa gender_id to 11, and run the same query, the result will be

array(4) {
  [0]=>
  array(4) {
    ["id"]=>
    string(3) "1"
    ["type"]=>
    string(6) "lisa"
    ["pass"]=>
    string(11) "good"
    ["gender"]=>
    string(1) "male"
    }
  [1]=>
  array(4) {
    ["id"]=>
    string(3) "2"
    ["type"]=>
    string(6) "jack"
    ["pass"]=>
    string(11) "pass"
    ["gender"]=>
    string(1) "male"
    }
  [2]=>
  array(4) {
    ["id"]=>
    string(3) "3"
    ["type"]=>
    string(6) "paul"
    ["pass"]=>
    string(11) "5555"
    ["gender"]=>
    string(1) "male"
    }
  [3]=>
  array(4) {
    ["id"]=>
    string(3) "4"
    ["type"]=>
    string(6) "rich"
    ["pass"]=>
    string(11) "none"
    ["gender"]=>
    string(1) "male"
    }

This result is how the rows are positioned in the table(user lisa first), which is represented correctly in the results as we did not specify any order.

Why is the first result set being ordered when we are not specifying this. It does not seem correct, I want the results to be pulled exactly how they are positioned in the table.

Thanks

Answer 1

In a relational database, there is no such thing as a "natural order". If you care about the order of your results, you have one and only one option-- specify an ORDER BY clause.

Without looking at the query plan, I would guess that your gender table is the driving table of a nested loop join which happens to return all the male rows first. It would be perfectly valid, though, for the order of results to change if the optimizer decides to use a different plan, if the underlying data changes, if you add and remove rows over time, etc. If you care about the order, specify an ORDER BY clause.

Adding an ORDER BY does not necessarily force the database to do any more work. It is entirely possible that it simply causes the database to choose a query plan that returns data in the order you want as a side effect.