Nested select statement in SQL Server

Question

Why doesn't the following work?

SELECT name FROM (SELECT name FROM agentinformation)

I guess my understanding of SQL is wrong because I would have thought this would return the same thing as

SELECT name FROM agentinformation

Doesn't the inner select statement create a result set which the outer SELECT statement then queries?

Answer 1

You should use the alias in the subquery like this:

SELECT ID FROM (SELECT ID FROM agent_information) s  

Or

More explicitly you can use this way:

SELECT s.ID FROM (SELECT ID FROM agent_information) s