Nested select statement in SQL Server

sql sql-server nested subquery querying
Brennan Vincent picture Brennan Vincent · Jan 7, 2011 · Viewed 717.2k times · Source

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

Joe Stefanelli picture Joe Stefanelli · Jan 7, 2011

You need to alias the subquery.

SELECT name FROM (SELECT name FROM agentinformation) a

or to be more explicit

SELECT a.name FROM (SELECT name FROM agentinformation) a

Related questions

Best way to do nested case statement logic in SQL Server

I'm writing an SQL Query, where a few of the columns returned need to be calculated depending on quite a lot of conditions. I'm currently using nested case statements, but its getting messy. Is there a better (more organised and/…

Read More
Can you create nested WITH clauses for Common Table Expressions?

WITH y AS ( WITH x AS ( SELECT * FROM MyTable ) SELECT * FROM x ) SELECT * FROM y Does something like this work? I tried it earlier but I couldn't get it to work.

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More