Something interesting I ran into a few days ago, were these new keywords (new to me) in Sql Server.

• SOME
• ANY
• ALL

ALL is used several places so, its not all too new (ex: REBUILD INDEXES, GRANT ALL, etc), but SOME and ANY are particularly new and interesting. What is the purpose? Could it not be done using IN clause?

Talking just about the intent of these clauses, SOME says that ‘return any matching records from parent query, where it matches with some of the result set from the subquery‘. It works in TRUE or FALSE logic. The left hand side (LHS) of SOME logic looks at the scalar list on the right hand side and says if the values in the LHS have matching with some of the scalars values on the RHS. Some is the key here, if there is a match, it returns TRUE orelse FALSE.

Similarly ANY and ALL have similar logic. ANY says, if there is even one match then returns TRUE. ALL needs it to match with all the scalar values. One other requirement is that for SOME/ANY the RHS needs to be a subquery.

There are some situations, we can replace SOME/ANY with IN clause. But there are situations where having SOME/ANY are more appropriate. Also, with SOME/ANY we can use more logical operators =, <, >, <>, etc than with IN clause.

Some Examples:

Example, where using IN clause makes more sense than using SOME. As you can see below, the execution plan is not very different either way. But keep in mind that actual execution plan for your data set might vary (execution plan below is based on a small data set). Let’s say you want to retrieve records that match with a list of scalar values:

This could be written using SOME like this:

Execution Plan:

Execution Plan difference

Hope this helps,
_Sqltimes