ALL (SQL)
Synopsis
scalar-expression comparison-operator ALL (subquery)
Arguments
| Argument | Description |
|---|---|
| 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. |
Description
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.
Examples
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
