Sub Query

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

    Agreed. Good explanation.

  • So simple question and I was wrong with answer "D" ?!

    Give me my point back !

    PS I'd be nice if someone checks QotD questions before their appear....

    MS SQL 2008 MCITP x 3
    MS SQL 2012 MCSE x 2

  • Hugo Kornelis (7/28/2014)


    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.

    I think there is so much discussion because so many of us picked "D" thinking (with great confidence) that we were correct. I'm still sticking to my guns on D being more correct than C but it doesn't really matter. It's just points!


  • I'm glad other folks are seeing the logical issue with the answers too. I almost blew my lid when it said D was incorrect. that's bs


Viewing 4 posts - 31 through 34 (of 34 total)

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