Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

closed as off-topic by Paul White, Mark Storey-Smith, RolandoMySQLDBA, ypercube, Max Vernon Feb 3 at 20:08

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White, Mark Storey-Smith, RolandoMySQLDBA, ypercube, Max Vernon
If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

up vote 4 down vote accepted

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.

share|improve this answer
    
Excellent comprehensive answer. The gender is driving the order. Thanks –  cecilli0n Feb 1 at 5:55
    
If there is no such thing as a "natural order" how would we ORDER BY the position in the database? We couldn't use ID, as if we swap the last row ID with the first, They stay in the same position. –  cecilli0n Feb 1 at 6:06
    
@cecilli0n - What does "position in the database" mean to you? The physical order of rows in a heap-organized table is not something you should ever depend on. If you have some idea of a "natural" order, you would need to have a column in the table that captures that information that allows you to sort the data. –  Justin Cave Feb 1 at 6:11
    
If we use a tool for example phpmyadmin to view the database, When we swap the last ID with the first ID, The rows stay in the same position, they do not move. (atleast from phpmyadmin point of view.) Unless what you are saying is it must be tool which is deciding this? –  cecilli0n Feb 1 at 6:19
    
@cecilli0n - I would guess that the tool has some sort of order_by column in a table and is ordering by that column or is storing the "correct" order somewhere else. It's also possible that the tool is relying on some behavior that is not guaranteed to be consistent. –  Justin Cave Feb 1 at 6:25
show 1 more comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.