Is there any particular reason why adding an additional join to an ordered table would reorder the result set?

Using a left join on the existing result set

table-valued function is similar to below (function returns @table):

        INSERT @table
        select col1 from table1
        order by col1, x, y
        INSERT @table
        select col1 from table1
        order by col1, x, y, z
  • Flagged as a duplicate because the best answer is in that question. Unless you explicitly ask for the result set in a certain order, there is no guarantee it will always appear in that order. – Mark Sinkinson Jun 30 '14 at 13:22
  • In case anyone was wondering: To be able to have the conditional ordering, I wound up just using some case statements for the order by's for the set created post join – Drew Jun 30 '14 at 19:13
  • And in SQL Server 2012 you'll find that even the order you observed without the join will likely no longer appear. – Martin Smith Jun 30 '14 at 20:47
  • @MartinSmith was in 2014, however the order without the join was maintained somehow? – Drew Jul 1 '14 at 0:46

There is no inherent order inside a table.

There is no pre-stablished order when you insert rows. If run for example the following query:

FROM   tblA A
       JOIN tblB B ON A.id = B.id

Without an ORDER BY, then no particular order will be used. If you want some particular sorted results, you must use an ORDER BY clause. You could use a primary key and sort according to its values for example. And doesn't matter if you add one or more JOIN to the statement, same answer, without ORDER BY you are not assure to get same order each time you run the query.

There are several questions/answers that could help you to get a better understanding of this particular point:

  • So I have a table-valued function that outputs a table in a specified order (order specified within function) upon left join a table, the results are reordered. Thoughts? – Drew Jun 30 '14 at 13:33
  • 1
    @Drew sounds about right. You're not ordering the overall result set, so there is no guarantee of the result order – Mark Sinkinson Jun 30 '14 at 13:36
  • As @MarkSinkinson points out, doesn't matter that the table-valued function returns a table with a specified order. Once you run the "outter" select without ORDER BY you are not guarantee to have same order on each run. – Yaroslav Jun 30 '14 at 13:38
  • 2
    @Drew How does the table-valued function indicate an ORDER BY? Because it also includes TOP? That ORDER BY only indicates which rows to include in the TOP, not how to present them. If you use silly tricks like TOP 100 PERCENT the optimizer actually discards both the TOP and the ORDER BY because it knows they are meaningless. It works the same way in a view - if you want an order, you need to specify that order on the OUTER query, not inside a view or function. – Aaron Bertrand Jun 30 '14 at 13:58
  • 1
    There is no such thing as "top-down" - you need to think about a table (even a table variable inside a tvf, which you should avoid and use an inline tvf vs. multi-statement tvf anyway) as an unordered bag of rows. Think about this: I'm in a room and drop a bunch of marbles on the floor. You enter the room, can you tell me which marble hit the floor first? No. If I paid attention, you could ask me, and I could hand you the marbles in that order. This is why you need to add an outer ORDER BY and have some column that can actually indicate the order you want. – Aaron Bertrand Jun 30 '14 at 19:53

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