11

In Postgres, ANY and SOME are synonyms when used on the right hand side of a predicate expression. For example, these are the same:

column = ANY (SELECT ...)
column = SOME (SELECT ...)

This is documented here:

http://www.postgresql.org/docs/9.1/static/functions-subquery.html#FUNCTIONS-SUBQUERY-ANY-SOME

I have observed ANY and SOME to be supported by at least these SQL DBMSs:

  • DB2
  • Derby
  • H2
  • HSQLDB
  • Ingres
  • MySQL
  • Oracle
  • Postgres
  • SQL Server
  • Sybase ASE
  • Sybase SQL Anywhere

Can I safely assume that all of those dialects (and others, too) treat ANY and SOME as synonyms or is there a subtle difference between the two keywords in any/some DBMS?

I have found this in the SQL92 definition:

<quantifier> ::= <all> | <some>
<all> ::= ALL
<some> ::= SOME | ANY

This doesn't say anything about the semantics of ANY and SOME. Later on in the document, only <some> is referenced, not the two keywords. I'm suspecting that there might be a subtle difference in NULL handling, for instance, at least in some DBMSs. Any/some pointer to a clear statement whether this can be assumed or not is welcome.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • 3
    +1 I didn't even know about SOME/ANY until this SO Question. Learn something new everyday! :-) – Kevin LaBranche Jan 07 '12 at 17:41
  • @klabranche: Yeah, it allows for very elegant subquerying! Postgres even accepts an [ANY(array), SOME(array), ALL(array)](http://www.postgresql.org/docs/9.1/static/functions-comparisons.html#AEN17416) syntax – Lukas Eder Jan 07 '12 at 17:45
  • Definitely something to put in the toolbelt. – Kevin LaBranche Jan 07 '12 at 17:58
  • 3
    @LukasEder - I've never found a use for these. `WHERE X > ANY(SELECT foo ...)` can easily be rewritten as `WHERE X > (SELECT MIN(foo) ...)` for example and in SQL Server the second one is more efficient. – Martin Smith Jan 07 '12 at 18:14
  • @MartinSmith: Good point. I'm trying to think of a clause that cannot be re-phrased in more common SQL, but I can't think of one... – Lukas Eder Jan 07 '12 at 18:17
  • Good to know about perf... so perhaps not knowing about SOME/ANY is a good thing. :-) – Kevin LaBranche Jan 07 '12 at 18:23
  • @klabranche: Well, that is probably database-specific. I can imagine formal transformation rules that *could* apply to automatically do what Martin suggested. I guess SQL Server just doesn't optimise that particular case... – Lukas Eder Jan 07 '12 at 18:27
  • 1
    @Martin Smith: Some conditions with quantifiers may look clearer than their possible replacements. For instance, `X <> SOME (SELECT Y FROM T)` seems definitely clearer to me than `EXISTS (SELECT * FROM T WHERE Y <> Z)`. Still I admit I've never used quantifiers myself except in exercises sometimes. – Andriy M Jan 07 '12 at 19:13
  • @AndriyM: That's a nice (albeit rare) use case... On the other hand, being used to writing semi-joins using `EXISTS`, I still prefer the second one.. – Lukas Eder Jan 08 '12 at 14:20
  • @LukasEder: So do I, and on the same grounds too. – Andriy M Jan 08 '12 at 14:27
  • @AndriyM: Finally found a use-case for the `ALL` quantifier: http://stackoverflow.com/questions/8825866/how-to-simulate-greatest-in-sybase-ase/9045290#9045290 – Lukas Eder Jan 28 '12 at 12:43
  • @LukasEder: Thanks for sharing! Would have been great if that could work in SQL Server, which, sadly, only allows quantifiers with subqueries, not with explicit value lists. – Andriy M Jan 28 '12 at 15:35
  • @AndriyM: Hah, true. According to the SQL standard, a `` is expected...
     – Lukas Eder Jan 28 '12 at 15:40
  • FWIW MS Access supports `ANY` and `SOME` and are synonyms. – onedaywhen Jan 30 '12 at 12:55

1 Answers1

5

Few lines after what you're quoting, the SQL92 standard also specifies the semantics for <some>, namely:

c) If the implied <comparison predicate> is true for at least
one row RT in T, then "R <comp op> <some> T" is true.

d) If T is empty or if the implied <comparison predicate> is
false for every row RT in T, then "R <comp op> <some> T" is
false.

e) If "R <comp op> <quantifier> T" is neither true nor false,
then it is unknown.

These rules apply for the <some> token, independent on whether it is the SOME or ANY alternative, so yes, they are synonyms according to the standard

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
voidengine
  • 2,504
  • 1
  • 17
  • 29
  • It looks like it, yes. As I said, `` is referenced, and not the keywords. But it doesn't say that the keywords always mean the same *explicitly*. After all, many databases have been observed to deviate from the standard every once in a while. E.g. Postgres: *"Note that if there are no successes and at least one right-hand row yields null for the operator's result, the result of the ANY construct will be null, not false. This is in accordance with SQL's normal rules for Boolean combinations of null values."* – Lukas Eder Jan 07 '12 at 17:51
  • well, that should follow the rule (e): There's no row for which the predicate was true, and at the same time it's not false for every row - it's unknown for the row with null. So neither (c) nor (d) can be applied and the result of the any/some operator is unknown – voidengine Jan 07 '12 at 18:00
  • I guess you're right... I'm 99% tending towards your opinion. Just waiting for someone who might've had any/some other experience :) – Lukas Eder Jan 07 '12 at 18:05