Sub Query

Question

Sub Query

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

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

Marcia J SSCertifiable Points: 5649 More actions · Answer 1

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.

Agreed. Good explanation.

Konstantin Reu Hall of Fame Points: 3149 More actions · Answer 2

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

Ken SSCoach Points: 17191 More actions · Answer 3

Hugo Kornelis (7/28/2014)
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.

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!

Ken

wordracr SSC Enthusiast Points: 176 More actions · Answer 4

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

😀