Sub Query

  • DO they just make up their OWN answers to these questions?

  • Another D-worshipper...

  • Revenant (7/25/2014)


    Another D-worshipper...

    I also picked D. Not sure if it's correct, but it seems the way the process would work.

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • I appear to have clicked an answer that I was sure I didn't click. I guess that didn't happen, so it ust have been finger trouble. I agree with all the comments above pointing out that a correlated subquery is one which uses values provided by an outer query.

    Tom

  • I was a D fan myself.

    Then I looked at the query plan for the example in the TechNet article at http://technet.microsoft.com/en-us/library/ms187638(v=sql.105).aspx. What it shows (at least in my AdventureWorks2008R2) is that the execution of a join based on the inner query providing input to the join in the outer query.

    This is reasonably interpreted as the outer query being dependent on the inner query. So I yield to C.

  • +1

  • robert.diley (7/25/2014)


    I was a D fan myself.

    Then I looked at the query plan for the example in the TechNet article at http://technet.microsoft.com/en-us/library/ms187638(v=sql.105).aspx. What it shows (at least in my AdventureWorks2008R2) is that the execution of a join based on the inner query providing input to the join in the outer query.

    This is reasonably interpreted as the outer query being dependent on the inner query. So I yield to C.

    The problem is that there's nothing intrinsic to a subquery that results in that specific exec plan. Like with normal joins, which order SQL decides to process them depends on statistics, row estimations and estimated costs., as well as where the correlated subquery is (WHERE, FROM on the right-hand-side APPLY or SELECT)

    Logically, you're guaranteed (for a correlated subquery) that the inner query's execution depends on the values from the outer query.

    Physically, you're guaranteed nothing about the way the query is processed. That depends on the optimiser's choices based on row estimations.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I do wish that questions were at least reviewed before being posted.

    Questions like this completely invalidate the point system. IMHO 😀

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Not so great question as it is wide open to interpretation and there is no reference backing up the explanation.

    (I choose D btw. A value of the current row of the outer query is passed to the inner query which is then executed)

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • sknox (7/25/2014)


    At its heart, a correlated subquery is simply one that refers to data from the outer query.

    These are (non-correlated) subqueries:

    SELECT DB_NAME()

    , (SELECT count(*) FROM sys.objects) AS TotalObjects

    , (SELECT count(*) FROM sys.indexes) as TotalIndexes

    The processing of each of these subqueries is independent of each other and the processing of the outer query, saving that the final results will not be returned before all queries are finished and merged.

    These are correlated subqueries:

    SELECT object_id

    , (SELECT count(*) from sys.indexes I WHERE I.object_id = O.object_id) as Indexes

    FROM sys.objects O

    WHERE EXISTS (SELECT 1 from sys.indexes I WHERE I.object_id = O.object_id)

    Now both subqueries are dependent upon information from the outer query, so (D) their processing will always be affected by the processing of the outer query.

    However, depending on the nature of the data and the query, the query optimizer may or may not change the processing of the outer query. It could process the outer query as written, use the resultset to process the subquery, and merge and filter the results, or it could reorganize the outer query and subqueries into joins. In my example, the correlated subquery in the SELECT clause might not affect the outer query at all; the correlated subquery in the WHERE clause probably will.

    So (D) is always true; (C) is possible but not necessarily true.

    + 1

  • I also chose D as the answer.

    The whole point of a correlated subquery is that the inner query depends on information given to it by the outer query to yield it's results.

    ---------------
    Mel. 😎

  • GilaMonster (7/25/2014)


    robert.diley (7/25/2014)


    I was a D fan myself.

    Then I looked at the query plan for the example in the TechNet article at http://technet.microsoft.com/en-us/library/ms187638(v=sql.105).aspx. What it shows (at least in my AdventureWorks2008R2) is that the execution of a join based on the inner query providing input to the join in the outer query.

    This is reasonably interpreted as the outer query being dependent on the inner query. So I yield to C.

    The problem is that there's nothing intrinsic to a subquery that results in that specific exec plan. Like with normal joins, which order SQL decides to process them depends on statistics, row estimations and estimated costs., as well as where the correlated subquery is (WHERE, FROM on the right-hand-side APPLY or SELECT)

    Logically, you're guaranteed (for a correlated subquery) that the inner query's execution depends on the values from the outer query.

    Physically, you're guaranteed nothing about the way the query is processed. That depends on the optimiser's choices based on row estimations.

    Totally agreed.

    ---------------
    Mel. 😎

  • SqlMel (7/27/2014)


    GilaMonster (7/25/2014)


    robert.diley (7/25/2014)


    I was a D fan myself.

    Then I looked at the query plan for the example in the TechNet article at http://technet.microsoft.com/en-us/library/ms187638(v=sql.105).aspx. What it shows (at least in my AdventureWorks2008R2) is that the execution of a join based on the inner query providing input to the join in the outer query.

    This is reasonably interpreted as the outer query being dependent on the inner query. So I yield to C.

    The problem is that there's nothing intrinsic to a subquery that results in that specific exec plan. Like with normal joins, which order SQL decides to process them depends on statistics, row estimations and estimated costs., as well as where the correlated subquery is (WHERE, FROM on the right-hand-side APPLY or SELECT)

    Logically, you're guaranteed (for a correlated subquery) that the inner query's execution depends on the values from the outer query.

    Physically, you're guaranteed nothing about the way the query is processed. That depends on the optimiser's choices based on row estimations.

    Totally agreed.

    +1

    Thanks

  • sknox (7/25/2014)


    At its heart, a correlated subquery is simply one that refers to data from the outer query.

    These are (non-correlated) subqueries:

    SELECT DB_NAME()

    , (SELECT count(*) FROM sys.objects) AS TotalObjects

    , (SELECT count(*) FROM sys.indexes) as TotalIndexes

    The processing of each of these subqueries is independent of each other and the processing of the outer query, saving that the final results will not be returned before all queries are finished and merged.

    These are correlated subqueries:

    SELECT object_id

    , (SELECT count(*) from sys.indexes I WHERE I.object_id = O.object_id) as Indexes

    FROM sys.objects O

    WHERE EXISTS (SELECT 1 from sys.indexes I WHERE I.object_id = O.object_id)

    Now both subqueries are dependent upon information from the outer query, so (D) their processing will always be affected by the processing of the outer query.

    However, depending on the nature of the data and the query, the query optimizer may or may not change the processing of the outer query. It could process the outer query as written, use the resultset to process the subquery, and merge and filter the results, or it could reorganize the outer query and subqueries into joins. In my example, the correlated subquery in the SELECT clause might not affect the outer query at all; the correlated subquery in the WHERE clause probably will.

    So (D) is always true; (C) is possible but not necessarily true.

    +1

  • LOL!!

    I simply cannot believe the amount of discussion over what can of course only be a simple mistake by the question author.

    The very definition of "correlated" is that the inner query references a column from the outer query and hence has to be (logically) re-evaluated for every row (or at least every distinct referenced value) from the outer query.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 15 posts - 16 through 30 (of 34 total)

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