July 24, 2014 at 10:38 pm
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 ?
July 25, 2014 at 1:04 am
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:
July 25, 2014 at 1:30 am
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??
July 25, 2014 at 1:39 am
robertjtjones (7/25/2014)
Agree with the above postsI 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.
July 25, 2014 at 1:58 am
🙂
July 25, 2014 at 2:40 am
Yes the answer should be D.
Can I have muy point please.
July 25, 2014 at 2:46 am
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.
July 25, 2014 at 3:06 am
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.
July 25, 2014 at 3:09 am
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
Follow me on
Twitter: @sqltwins
July 25, 2014 at 6:30 am
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.
July 25, 2014 at 6:58 am
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.
July 25, 2014 at 7:35 am
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.
July 25, 2014 at 7:37 am
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
July 25, 2014 at 7:52 am
I was thinking D as many of you did.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply