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.

This question already has an answer here:

I have one question that confusing me.

By logical query processing, the SELECT clause comes before the ORDER BY clause, and in theory, every step in a query creates a virtual table. So, how is it even possible to order result by a column that is not listed in the SELECT clause, when the SELECT clause generates a virtual table with the selected columns?

In case your query has a GROUP BY clause, then the behavior become normal, but in the absence of a GROUP BY clause, it seems a little tricky. Does anybody have an explanation?

share|improve this question
4  
Imagine that the SELECT step produces a virtual table that includes also some "shadow" columns/expressions (all those that are in the ORDER BY clause but not in the SELECT list). These shadow columns can be used for the ordering but are not included in the final results. –  ypercube Mar 28 '13 at 7:07
    
@ypercube: this should be an answer... –  gbn Mar 28 '13 at 10:21
    
@ypercube Please, post your comment as an answer, and add some additional explanation, of course, if you had –  veljasije Mar 28 '13 at 11:12
add comment

marked as duplicate by Martin Smith, Mark Storey-Smith, Thomas Stringer, Max Vernon, Paul White Mar 28 '13 at 22:42

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2 Answers

The column specified in the ORDER BY is always retrieved from its underlying table even the specified column is not specified in the select list. An example to show the behavior,

Execute the T-SQL below and include action execution plan,

USE AdventureWorks;

SELECT Title, LastName
FROM Person.Person
WHERE PersonType = 'EM'
ORDER BY FirstName;

In execution plan, the output list of the clustered index scan operator shows 'Title', 'FirstName', and 'LastName'. Then the result set is sorted in the sort operator before returning the ordered result with the specified select column in select operator.

share|improve this answer
    
How you explain using column aliases from select list in ORDER BY clause, if ORDER BY was first executed? –  veljasije Mar 28 '13 at 19:21
    
The columns, including ORDER BY column (table column) are read from the table FIRST, then sorted, then returned only the result set with columns that are specified in the SELECT clause. In case of the column alias (e.g. LastName + ' Jr.' AS JrName and ORDER BY JrName), the sequence of the execution plan is read data from the table, compute scalar (compute JrName), order by and return result set. –  Travis Gan Mar 29 '13 at 2:34
add comment

If you have something in your ORDER BY clauses then it will be included in the relevant internal structures as if they were in the SELECT list - this definitely counts for base fields not otherwise being output by the statement and may count for computed values too. It just doesn't output these "extra" columns in the final stage.

If it didn't include the columns from the ORDER BY clause in those internal structures, it would have to requery the source tables at the final state to retrieve the ordering data which would be far far less efficient in the vast majority of cases. The only time this might make sense is if you are ordering by the result of a correlated sub-query that the query planner can't map to something less inconvenient, in that case you want it to do that part last once it knows which rows are being output (no point running a complex sub-query for many rows that are going to be filtered out by other clauses later when it can just run it for the few rows being output).

share|improve this answer
add comment

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