ChatGPT解决这个技术问题 Extra ChatGPT

Nested select statement in SQL Server

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?


J
Joe Stefanelli

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  

Make sure your alias is somewhat verbose too! I love when I get to work on a wuery with t1,t2,t3,t4,t5,t6
Where would a where clause go for the outer query?
@ColonelPanic: The WHERE clause for the outer query would be tacked on at the very end.
Oracle accepts the first select without the alias.
U
Uwe Keim

The answer provided by Joe Stefanelli is already correct.

SELECT name FROM (SELECT name FROM agentinformation) as a  

We need to make an alias of the subquery because a query needs a table object which we will get from making an alias for the subquery. Conceptually, the subquery results are substituted into the outer query. As we need a table object in the outer query, we need to make an alias of the inner query.

Statements that include a subquery usually take one of these forms:

WHERE expression [NOT] IN (subquery)

WHERE expression comparison_operator [ANY | ALL] (subquery)

WHERE [NOT] EXISTS (subquery)

Check for more subquery rules and subquery types.

More examples of Nested Subqueries.

IN / NOT IN – This operator takes the output of the inner query after the inner query gets executed which can be zero or more values and sends it to the outer query. The outer query then fetches all the matching [IN operator] or non matching [NOT IN operator] rows. ANY – [>ANY or ANY operator takes the list of values produced by the 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.

ALL – [>ALL or ALL operator takes the list of values produced by the inner query and fetches all the values which are greater than the maximum of the list. The

e.g. >ALL(100,200,300), the ALL operator will fetch all the values greater than 300.

EXISTS – The EXISTS keyword produces a Boolean value [TRUE/FALSE]. This EXISTS checks the existence of the rows returned by the sub query.