Ambigous column name

Question

I'm confused by the behavior of ORDER BY with SQL Server 2014.

I have a query like this:

select a.Name, b.Status
from a
join b on a.parent_id = b.parent_id
order by parent_id

This query will throw an error

Ambiguous column name 'parent_id'

which makes sense because the column appears in both tables. What I don't understand is that if I list one of the those columns in the SELECT like so:

select a.Name, b.*
from a
join b on a.parent_id = b.parent_id
order by parent_id

it works.

I understood ORDER BY to be a 'last in' type of operation, which is why you can order by a column ordinal. And I understood that you could order by a column not listed in the SELECT clause. So, why does it throw an error in one case and not the other? And is the ordering different if I were to list the column from table a in the SELECT vs listing it in the ORDER BY from table b?

EDIT: the answers to this question: Ambiguous Column Name: Why is it ambiguous in certain circumstances? explains my problem, but just to be clear, it's not version dependent like that question implies.

Answer 1

When you say order by parent_id, then the SQL engine has to look for parent_id. There are two possibilities:

• The FROM clause
• The SELECT clause

Without a table alias, SQL Server will look in the SELECT first. Voila! If it finds parent_id there, then it is done. It only detects the duplicate if it has to go to the FROM clause for resolution.