Join reorders results

Question

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):

conditional
    BEGIN
        INSERT @table
               (col1)
        select col1 from table1
        order by col1, x, y
    END
  ELSE
    BEGIN
        INSERT @table
               (col1)
        select col1 from table1
        order by col1, x, y, z
    END

Answer 1

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:

SELECT A.A, B.B
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:

• a really deep and detailed answer here on dba.stackexchange.com: Storage order vs Result order
• You cannot guarantee the order unless you specifically provide an order by with the query
• In the SQL world, order is not an inherent property of a set of data.
• Without ORDER BY, You Can’t Depend On the Order of Results great blog entry by Michael J. Swart