Menu
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:
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.
571
While not an exhaustive guide on every possible variation or the depth of their nuances, the basic differences between dialects implemented by Microsoft (T-SQL), Oracle (PL/SQL), and PostgreSQL (PL/sgSQL) will be explored. Additionally, some other variants will be briefly covered. Transact-SQL, or T-SQL for short, is Microsoft’s extension of SQL.
The SQL ALL Operator. The ALL operator: returns a boolean value as a result. returns TRUE if ALL of the subquery values meet the condition. is used with SELECT, WHERE and HAVING statements. ALL means that the condition will be true only if the operation is true for all values in the range.
A SYNONYM provides another name for database object, referred to as original object, that may exist on a local or another server. A synonym belongs to schema, name of synonym should be unique.
The ANY and ALL operators are used with a WHERE or HAVING clause. The ANY operator returns true if any of the subquery values meet the condition. The ALL operator returns true if all of the subquery values meet the condition.
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
149
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
