SOME

  • KWymore (3/21/2012)


    I had only looked at the ANY and SOME syntax once before and did not see a need for them. They seem to perform exactly like the IN statement unless I am missing something. Are there any instances where you could not perform the same logic using IN instead of ANY?

    You can use inequality comparisons with Any/Some. For example:

    SELECT *

    FROM ( VALUES ( 1), ( 2), ( 3) ) AS MyQuery (MyCol)

    WHERE MyCol > ANY ( SELECT MyCol2

    FROM ( VALUES ( 2), ( 3) ) AS MyQuery2 (MyCol2) ) ;

    Note the use of ">ANY". IN will only do equality comparisons.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • KWymore (3/21/2012)


    Are there any instances where you could not perform the same logic using IN instead of ANY?

    Yes, as GSquared already shows.

    However, there are (as far as I know) no cases where an ANY or SOME could not be replaced by an equivalent EXISTS subquery.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Nice question.

    -----------------
    Gobikannan

  • Hugo Kornelis (3/21/2012)


    KWymore (3/21/2012)


    Are there any instances where you could not perform the same logic using IN instead of ANY?

    Yes, as GSquared already shows.

    However, there are (as far as I know) no cases where an ANY or SOME could not be replaced by an equivalent EXISTS subquery.

    There are always alternatives to any particular query syntax. These options (Any/Some/All) can make a query significantly more readable than some complex Exists subqueries, in the right cases.

    I haven't tested them for performance, nor have I used them with any frequency, so can't speak to that point, but a QotD that highlights a less-known feature for possible use, is perfectly valid.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared (3/21/2012)


    Hugo Kornelis (3/21/2012)


    KWymore (3/21/2012)


    Are there any instances where you could not perform the same logic using IN instead of ANY?

    Yes, as GSquared already shows.

    However, there are (as far as I know) no cases where an ANY or SOME could not be replaced by an equivalent EXISTS subquery.

    There are always alternatives to any particular query syntax. These options (Any/Some/All) can make a query significantly more readable than some complex Exists subqueries, in the right cases.

    I haven't tested them for performance, nor have I used them with any frequency, so can't speak to that point, but a QotD that highlights a less-known feature for possible use, is perfectly valid.

    I would end up using Exists/Not Exists for these scenarios. However, I do see your point on readability as I have written some fairly complex, ugly Exists statements before. I will have to keep ANY/ALL in mind then for these scenarios.

  • GSquared (3/21/2012)


    Hugo Kornelis (3/21/2012)


    KWymore (3/21/2012)


    Are there any instances where you could not perform the same logic using IN instead of ANY?

    Yes, as GSquared already shows.

    However, there are (as far as I know) no cases where an ANY or SOME could not be replaced by an equivalent EXISTS subquery.

    There are always alternatives to any particular query syntax. These options (Any/Some/All) can make a query significantly more readable than some complex Exists subqueries, in the right cases.

    I haven't tested them for performance, nor have I used them with any frequency, so can't speak to that point, but a QotD that highlights a less-known feature for possible use, is perfectly valid.

    I wonder, do they produce the same execution plan?

  • thanks for the question Steve.

  • Never used ANY or SOME like this and have learned something new.:w00t:

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Got it wrong this time because I thought the date string would not be explicitly converted, I think I will run the following statement in my mind:

    USE SQLSERVER2008R2

    SET ALWAYS_EXPLICIT_CONVERSION ON

    GO

    Though I have never used SOME, ANY or ALL, this is an interesting question that brings new knowledge.

    Thank you Steve for the question.

    "El" Jerry.

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

    To better understand your help request, please follow these best practices.[/url]

  • Nice question.

    I don't think I've ever used ANY - certainly not with = or != or <> and think not with any of the other comparisons, although I can see that it would sometimes make more readable code than using exists. Certainly haven't used SOME (too lazy to type 4 characters where 3 will do).

    Tom

  • Nice & Easy question 🙂

    Thanks

  • Brilliant question! A bit tricky initially ... had to read between the lines of "ANY" returning a true for all rows in the table.

    Kwex.

  • Nice question and answer explanation. Thank you for covering the ANY operator since I learnt more.

  • Have to read carefully to make sure you understand the question. Got my point but had to think about it. Thanks for submitting.

    http://brittcluff.blogspot.com/

  • Nice question! Learned something new.:-)


    Sujeet Singh

Viewing 15 posts - 16 through 30 (of 35 total)

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