ALL - InterSystems SQL Reference - InterSystems IRIS Data Platform 2018.1

scalar-expression comparison-operator ALL (subquery)

scalar-expression	A scalar expression (most commonly a data column) whose values are being compared with the result set generated by the 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 from a single column that is used for the comparison with scalar-expression.

The ALL 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 all of the corresponding values retrieved by the subquery. The ALL 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.

ALL 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 ALL 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 ages in the Person database that are less than all of the ages in the Employee database:

SELECT DISTINCT Age FROM Sample.Person
WHERE Age < ALL
   (SELECT Age FROM Sample.Employee)
ORDER BY Age

The following example selects those names in the Person database that are longer or shorter than all of the names in the Employee database:

SELECT $LENGTH(Name) AS NameLength,Name FROM Sample.Person
WHERE $LENGTH(Name) > ALL
     (SELECT $LENGTH(Name) FROM Sample.Employee)
OR $LENGTH(Name) < ALL
     (SELECT $LENGTH(Name) FROM Sample.Employee)

The following example returns a list of states west of the Mississippi River, all of which states do not contain an employee with the title of Manager or Director:

SELECT DISTINCT State
FROM Sample.USZipCode
WHERE Longitude < -93
  AND State != ALL
   (SELECT Home_State FROM Sample.Employee
    WHERE Title [ 'Manager' OR Title [ 'Director')
ORDER BY State