ANY vs. SOME Operators

Special care must be taken when performing test conditions with the ANY special operator, since the test may not conform to the common English interpretation. For example, while the English phrase "not equal any" would normally be evaluated as true if there are no values that match, the interpretation of the SQL equivalent operator !=ANY (or ^=ANY) is different. SQL interprets !=ANY to mean that the comparison is true, if there is at least one value that does not match.

The English equivalent for the SQL condition represented by !=ANY is, in fact, "any not equal". Since this test operator is equivalent to the more readily understood NOT IN, we recommend using NOT IN instead of !=ANY. Similar caution is required when using any other operator in conjunction with ANY. We recommended that the SOME special operator be used in place of ANY. The result is the same, however, the query is more intuitively understood.

The difference between the SOME and ALL special operators is illustrated with the nested query below. This query (except for the keyword SOME) is identical to the previous nested query (which used ALL):

SELECT sno, pno
FROM partsupp
WHERE qty > SOME
  (SELECT qty
  FROM partsupp
  WHERE pno = "P1"
    OR pno = "P6")

The following results illustrate how the SOME special operator retrieves a much larger subset of data than the ALL operator: