Sub Query

  • Comments posted to this topic are about the item Sub Query

  • Certainly I missed something, but what about http://technet.microsoft.com/en-us/library/ms187638%28v=sql.105%29.aspx ?

    In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values.

    Does il not mean that D is a correct answer ?

  • Roland C (7/24/2014)


    Certainly I missed something, but what about http://technet.microsoft.com/en-us/library/ms187638%28v=sql.105%29.aspx ?

    In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values.

    Does il not mean that D is a correct answer ?

    +1

    Confused :unsure:

  • Agree with the above posts

    I assume that the point is supposed to be that the inner query result affects the rows returned in the outer query - but this answer only seems appropriate for a correlated subquery that is in the WHERE clause of the outer query.

    What about when the subquery is in the select clause??

  • robertjtjones (7/25/2014)


    Agree with the above posts

    I assume that the point is supposed to be that the inner query result affects the rows returned in the outer query - but this answer only seems appropriate for a correlated subquery that is in the WHERE clause of the outer query.

    What about when the subquery is in the select clause??

    I think it's similar. But the results of the outer query are updated with the results of the repeating inner query instead of being filtrated according to these results.

  • 🙂

  • Yes the answer should be D.

    Can I have muy point please.

  • Roland C (7/24/2014)


    Certainly I missed something, but what about http://technet.microsoft.com/en-us/library/ms187638%28v=sql.105%29.aspx ?

    In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values.

    Does il not mean that D is a correct answer ?

    (..Digging little deeper from the link you posted)

    As there are more than one way to construct subqueries http://technet.microsoft.com/en-us/library/ms175838(v=sql.105).aspx, so I guess , well "it depends".

    And at this link http://technet.microsoft.com/en-us/library/ms189575(v=sql.105).aspx, the example in the second block yields the same results and identical AEP,. it also states...

    QUOTE

    Many Transact-SQL statements that include subqueries can be alternatively formulated as joins. Other questions can be posed only with subqueries. In Transact-SQL, there is usually no performance difference between a statement that includes a subquery and a semantically equivalent version that does not. However, in some cases where existence must be checked, a join yields better performance. Otherwise, the nested query must be processed for each result of the outer query to ensure elimination of duplicates.

    UNQUOTE

    from my angle it looks like... depending on the query how it is constructed, it changes its behaviour of processing.

    edit:// fixed the typo

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Raghavendra Mudugal (7/25/2014)


    from my angle it looks like... depending on the query how it is constructed, it changes its behaviour of processing.

    Maybe this is why I wrote "a correct answer", not "the correct answer";-)

    Certainly, the result of the outer query will depend on the results of the inner, otherwise the inner would be completely useless. 😀

    This said, here we are discussing of correlated subqueries, which makes a lot of difference.

  • Agree with the posts above. It's the "outer" query that determines the execution of the "inner" query.

    The TechNet Link shared (http://technet.microsoft.com/en-us/library/ms187638%28v=sql.105%29.aspx) has sufficient proof for "D" to be the right answer.

    Points, please 🙂

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

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

  • On a logical level, I'd say the question was correct. You know the output of the inner query before the outer query because the inner query exists solely as a constraint on the output of the outer one. In terms of how the db actually executes it however, it seems very much like an "it depends" question.

  • Nevyn (7/25/2014)


    You know the output of the inner query before the outer query...

    That may be true of a non-correlated subquery, but a correlated subquery depends upon some information from the outer query, so you cannot know its output unless you know the information being passed from the outer query.

    ... because the inner query exists solely as a constraint on the output of the outer one.

    No. There are many uses for correlated subqueries, and while a filter/constraint on the output of the outer query is one, it's not the only one.

  • I figured what the question was going for but I agree the correct answer should have been D. I saw the problem as the specific wording of RESULT. The whole issue with a correlated sub-query is the dependency of the inner query on a candidate row value set (not the entire result set) in order to process and return its result (typically a single value) to the outer query.

    There are no facts, only interpretations.
    Friedrich Nietzsche

  • I was thinking D as many of you did.

Viewing 15 posts - 1 through 15 (of 34 total)

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