Does join change the sorting of original table?

Does join change the sorting of original table?

For example

SELECT * FROM #empScheduled   

AND

    SELECT es.job_code,es.shift_id, es.unit_id,jd.job_description,s.shift_description,u.unit_code,
     [group] = (SELECT job_description FROM dbo.job_desc WHERE job_code = jd.job_group_with),es.new_group 
     FROM #empScheduled es JOIN job_desc jd ON es.job_code = jd.job_code 
JOIN dbo.shifts s ON es.shift_id = s.shift_id JOIN dbo.units u 
ON es.unit_id = u.unit_id 

Will produce the same records but in different sorting orders! Why does that happen and what is the way to stop it and produce the result in same sort order as t开发者_开发技巧he orginal table WITHOUT having to use Order by ? Thanks.

If you don't specify an ORDER BY, there is no defined order on the result tuples. A simple SELECT * FROM x will return the tuples in the order they are stored, since only a table scan is done. However, if a join or similar is done, there is no way of knowing how the intermediate results are handled and the order of the results is undefined.

It is never a good idea to want the result "in the same order as the original table", since this is not a definition of an order. If you want to preserve the insert order, use an ascending primary key and simply order by that. This way, you can always restore the "original" order of your tuples.

There is no inherent sort order in SQL Server.

In your case, it is likely due to indexes on your joined table and their sorted order.

If you want consistently sorted results, you must use an ORDER BY clause.

You might try the FORCE ORDER hint to explicitly force the join order. Otherwise the optimizer can order the joins any way it thinks is best. Not sure why you cannot use ORDER BY though.