Mark Fitzgerald-331224 (10/20/2015)
What am I missing here?
The CustomerId in the subqueries Select list and Order By has not been qualified with the table alias, so would give an ambiguous column error. So the query would normally return an error whatever the programmer intended.
In this case there is no error, but why as both S. and C. table aliases are used for CustomerID in the subquery.
When multiple tables are joined, and they share column names, SQL Server has no rule to determine what table an unqualified column name applies to. So if there is more than just one potential candidate, it throws an error.
But when queries are nested, in subqueries, derived tables, CTEs or other similar constructions, there is a hieararchy. SQL Server will always start at the current nesting level and look for matches there. If multiple possible matches are found *in the same (sub)query*, an error is thrown. If exatly one match is found, it is used. If no matches are found, SQL Server repeats the same process at the next lower nesting level.
In this case, the subquery has just a single table, so all unqualified columns are assumed to be from that table. (If they exist in that table, of course - otherwise they are searched in the tables used in the main query).