Ensuring result order when joining, without using an order_by

Question

I've got a mysql plugin that will return a result set in a specified order. Part of the result set includes a foreign key, and I'd like join on that key, while ensuring the order remains the same.

If I do something like:

    select f.id, 
           f.title 
      from sphinx s 
inner join foo f on s.id = f.id 
     where query='test;filter=type,2;sort=attr_asc:stitle';

It looks like I'm getting my results back in the order that sphinx returns them. Is this a quirk of mysql, or am I assured that a join won't change the order?

Answer 1

I don't believe that sql guarantees which table drives the ultimate sort order. Having said that, unless I would be very surprised if MySQL rewrites your query in such a way that the order changes.

Answer 2

SQL makes no guarantees about the result set order of a SELECT, which includes joins.

Answer 3

If you need a guaranteed order in the results of a query, use ORDER BY. Anything else is wishful thinking.

To give some insight on this, many databases divide execution steps in a way that can vary depending on the execution plan of the query, the amount of available CPU, and the kinds of optimizations the database can infer are safe. If a query is run in parallel on multiple threads the results can vary. If the explain plan changes, the results can vary. If multiple queries are running simultaneously, the results can vary. If some data is cached in memory, the results can vary.

If you need to guarantee an order, use ORDER BY.

Answer 4

You cannot do that, SQL does not guarantee the order after such operation.

Answer 5

There is no specific order guaranteed unless you specify an ORDER BY statement. Since you mentioned that you were using a plugin that returns result sets in a specified order, I'm assuming that that plugin generates SQL that will add the ORDER BY statement. If you do joins, one thing to look out for is the column names of the tables you're joining on. If they're named the same, your query might brake or order by a different column than intended.