InterSystems IRIS Data Platform 2019.2  /  InterSystems SQL Reference

InterSystems SQL Reference
Previous section           Next section
InterSystems: The power behind what matters   

Matches a value with at least one matching value from a subquery.
scalar-expression comparison-operator ANY (subquery)
scalar-expression A scalar expression (most commonly a data column) whose values are being compared with the result set generated by subquery.
comparison-operator One of the following comparison operators: = (equal to), <> or != (not equal to), < (less than), <= (less than or equal to), > (greater than), >= (greater than or equal to), [ (contains), or ] (follows).
subquery A subquery, enclosed in parentheses, which returns a result set that is used for the comparison with scalar-expression.
The ANY keyword works in conjunction with a comparison operator to create a predicate (a quantified comparison condition) that is true if the value of a scalar expression matches one or more of the corresponding values retrieved by the subquery. The ANY predicate compares a single scalar-expression item with a single subquery SELECT item. A subquery with more than one select item generates an SQLCODE -10 error.
The ANY and SOME keywords are synonyms.
ANY can be used wherever a predicate condition can be specified, as described in the Overview of Predicates page of this manual.
Where applicable, the system automatically applies Set-Valued Subquery Optimization (SVSO) to an ANY subquery. For details on this optimization, and using the %NOSVSO keyword to override it, refer to “Query Optimization Options” on the FROM clause reference page.
The following example selects those employees with salaries greater than $75,000 that live in any of the states west of the Mississippi River:
SELECT Name,Salary,Home_State FROM Sample.Employee
WHERE Salary > 75000
AND Home_State = ANY
 (SELECT State FROM Sample.USZipCode
  WHERE Longitude < -93)
ORDER BY Home_State
See Also

Previous section           Next section
Send us comments on this page
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-08-23 06:48:00