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

User · Accepted Answer

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

User · Answer

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

[sql] Nested select statement in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to nested

Examples related to subquery

Examples related to querying