Stairway to T-SQL Part 2: Beyond T-SQL Basics: Level 2: Writing Subqueries

  • I didn't realize you could use a subquery in all those places such as the HAVING clause.

  • Using the subquery in Function call

    it could be simply--

    SELECT SalesOrderID

    ,OrderDate

    ,DATEDIFF(DAY, OrderDate, (SELECT MAX(OrderDate)

    FROM Sales.SalesOrderHeader)

    ) AS DayDiff

    FROM Sales.SalesOrderHeader

  • The answer to question 1 should be "None of the above." By definition, a correlated subquery is a subquery. Since none of the answers applies to ALL subqueries, there is no valid answer to question 1.

    You also missed another case where subqueries can return multiple rows with a single column: on the right side of a conditional expression when used with the keyword SOME, ANY, or ALL.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Could someone shed more light on the following code? What kind of scenario can this be used in a real world?

    SELECT TOP (SELECT TOP 1 OrderQty

    FROM [Sales].[SalesOrderDetail]

    ORDER BY ModifiedDate) *

    FROM [Sales].[SalesOrderDetail]

    WHERE ProductID = 716;

    -----------------------------------------------------------------------
    Known is a DROP, Unknown is an OCEAN.:ermm:

  • Upon my execution of Listing 12, I got the following results. So, I am not sure if I should agree with of using a JOIN is same as Subquery.

    (1076 row(s) affected)

    Table 'SalesOrderDetail'. Scan count 1, logical reads 3310, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Product'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 31 ms, elapsed time = 111 ms.

    (1076 row(s) affected)

    Table 'SalesOrderDetail'. Scan count 1, logical reads 3310, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Product'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 80 ms.

    PS: I am using SQL Server 2014.

    -----------------------------------------------------------------------
    Known is a DROP, Unknown is an OCEAN.:ermm:

  • What other Stairways should one take prior to this one? There's a reference to the Stairway to T-SQL DML Stairway, which I've finished, but in this article, there is also this statement:

    Here are some other things to consider when using a subquery:

    ntext, text and image data types are not allowed to be returned from a subqueries

    The ORDER BY clause cannot be used in a subquery unless the TOP operator is used

    Views that use a subquery can’t be updated

    COMPUTE and INTO clauses cannot be used in a subquery

    However, I've done a search on all of the Stairway to T-SQL DML articles, and there is no mention or example of the COMPUTE clause. Is there another Stairway to read prior to this one? Or is there a basic set of T-SQL commands we should know prior to starting this one?

    Thanks.

  • @DataJoe - I don't see anywhere that the author says what the number returned to the TOP function actually is. The example just shows that some number will be returned and used as the argument to TOP, not what that number is.

    Where does it say that the number is 3?

  • jetboy2k (9/5/2016)


    What other Stairways should one take prior to this one? There's a reference to the Stairway to T-SQL DML Stairway, which I've finished, but in this article, there is also this statement:

    Here are some other things to consider when using a subquery:

    ntext, text and image data types are not allowed to be returned from a subqueries

    The ORDER BY clause cannot be used in a subquery unless the TOP operator is used

    Views that use a subquery can’t be updated

    COMPUTE and INTO clauses cannot be used in a subquery

    However, I've done a search on all of the Stairway to T-SQL DML articles, and there is no mention or example of the COMPUTE clause. Is there another Stairway to read prior to this one? Or is there a basic set of T-SQL commands we should know prior to starting this one?

    I'm guessing that he's just listing all clauses that cannot be used in a subquery. Since COMPUTE is a clause that exists, and cannot be used in a subquery, it's included in the list. I don't think he's trying to imply that it's a clause that you should already know all about. 🙂

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply