none
subselect in SELECT vs. in FROM as a derived table?

    Question

  • I'm trying to get a better understanding of when I would put a subselect inside the SELECT clause vs. putting it in the FROM clause and making it a derived table?  Any general comments on this?  Thanks.
    Thursday, June 20, 2013 4:15 PM

Answers

  • There are generally no "rules" that you can always apply.  I think the larger point to consider is that there are situations where you can use either approach and that you know how to use them in a query.  In a situation where you can use either approach, it is important to have the ability to evaluate the performance and choose the better one. 
    Thursday, June 20, 2013 5:11 PM

All replies

  • Hi rick7072

    A subquery is a SELECT statement that is nested within another statement – that’s why it’s called a subquery, because it’s like having a query within another query . Subqueries are usually used in the WHERE clause as a way to filter out certain rows returned in the result set of the outer query.

    while

    A derived table is basically a subquery, except it is always in the FROM clause of a SQL statement. The reason it is called a derived table is because it essentially functions as a table as far as the entire query is concerned.

    But, remember that a derived table only exists in the query in which it is created. So, derived tables are not actually part of the database schema because they are not real tables.

    Thursday, June 20, 2013 4:26 PM
  • Very different purposes.

    A subquery in the SELECT list is a scalar query, which returns exactly one column and one row. Such a subquery (one column, one row) can be used anywhere where you can have an expression (a value).

    A subquery that returns any number of columns (and rows) you can have in the FROM clause and you treat it just as a table. We call this a derived table. In 2005, we got a different syntax for achieving the same ting: Common Table Expression (CTE).


    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, June 20, 2013 4:33 PM
    Moderator
  • There are generally no "rules" that you can always apply.  I think the larger point to consider is that there are situations where you can use either approach and that you know how to use them in a query.  In a situation where you can use either approach, it is important to have the ability to evaluate the performance and choose the better one. 
    Thursday, June 20, 2013 5:11 PM