July 25, 2014 at 9:52 am
DO they just make up their OWN answers to these questions?
July 25, 2014 at 10:00 am
Another D-worshipper...
July 25, 2014 at 11:04 am
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
July 25, 2014 at 11:09 am
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
July 25, 2014 at 11:28 am
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.
July 25, 2014 at 12:41 pm
+1
July 25, 2014 at 1:36 pm
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
July 25, 2014 at 2:48 pm
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. SelburgJuly 26, 2014 at 3:21 am
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
July 27, 2014 at 4:31 am
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
July 27, 2014 at 9:14 am
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. 😎
July 27, 2014 at 9:17 am
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. 😎
July 28, 2014 at 5:10 am
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
July 28, 2014 at 7:12 am
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
July 28, 2014 at 7:23 am
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.
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply