October 10, 2012 at 1:26 am
can anyone explain why it returns all the rows as I am not cleared with the explanation ?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 10, 2012 at 3:43 am
kapil190588 (10/10/2012)
can anyone explain why it returns all the rows as I am not cleared with the explanation ?
ANY is not a very well known keyword, probably because it (and it's friends SOME and ALL) can always be replaced by another expression, so it's not needed. However, the ANY keyword means that the expression has to be true for at least one of the values in the subquery. So if you write (expression) > ANY (subquery), the expression has to be larger than at least one result in the subquery.
In this case, the use was (expression) = ANY (subquery), so this is true if at least one value in the subquery is equal to the expression. The subquery is not correlated, so it will return the same result for every row in the outer query: the set of all dates in the SalesDates table. And for each row in the SalesDates table, the WHERE clause evaluates to true if the date '2012/1/3' (which, BTW, is in an ambiguous format) is equal to at least one of the dates in the SalesDates table. That is the case, so for each row the WHERE clause evaluates to true, and each row will be returned.
October 10, 2012 at 3:54 am
thanks Hugo...
I am daily learning new things from your comments...
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 10, 2012 at 6:36 am
Hugo Kornelis (10/10/2012)
However, the ANY keyword means that the expression has to be true for at least one of the values in the subquery. So if you write (expression) > ANY (subquery), there has to be at least one result in the subquery that is larger than the expression.
That is wrong, (expression) > ANY (subquery) means "the expression is greater than at least one of the results of the subquery"; your description describes (expression) < ANY (subquery).
Tom
October 10, 2012 at 7:22 am
L' Eomot Inversé (10/10/2012)
Hugo Kornelis (10/10/2012)
However, the ANY keyword means that the expression has to be true for at least one of the values in the subquery. So if you write (expression) > ANY (subquery), there has to be at least one result in the subquery that is larger than the expression.That is wrong, (expression) > ANY (subquery) means "the expression is greater than at least one of the results of the subquery"; your description describes (expression) < ANY (subquery).
I edited my post to correct the error. Thanks for pointing this out to me, Tom!
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply