Can someone help me understand why the following doesn't 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?
You need to alias the subquery.
or to be more explicit
Answer provided by Joe Stefanelli is already correct.
We need to make alias of subquery because query needs table object which we will get from making an alias to subquery. Conceptually, the subquery results are substituted into the outer query. As we need table object in outer query, we need to make an alias of inner query.
Statements that include a subquery usually take one of these formats:
Check for more subquery rules and subquery types.
More examples of Nested Subquery.
IN / NOT IN – This operator takes the output of inner query after inner query gets executed which can be zero or more values and send it to outer query. The outer query then fetches all the matching [IN operator] or not non matching [NOT IN operator] rows.
ANY – [>ANY or ANY operator takes the list of values produced by inner query and fetches all the values which are greater than the minimum value of the list. The
e.g. >ANY(100,200,300), the ANY operator will fetch all the values greater than 100.
e.g. >ALL(100,200,300), the ALL operator will fetch all the values greater than 300.