How does ORDER BY really work?
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.