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?

Antisthenes

Answer 1

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  

Oestradiol

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 – Zanazander

Where would a where clause go for the outer query? – Vibraharp

@ColonelPanic: The WHERE clause for the outer query would be tacked on at the very end. – Oestradiol

Oracle accepts the first select without the alias. – Ranique

Answer 2

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.

1. 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.

2. 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.

3. 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.

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

Rana

Answer 3

TRY THIS

'select *,(SELECT count(id) FROM products WHERE user_id = users.id) as products_count from users ORDER BY products_count DESC, ID DESC LIMIT 200

Alonso