ALL (SQL)

Synopsis

scalar-expression comparison-operator ALL (subquery)

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 Overview of Predicates.

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.

Arguments

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

See Also

• SELECT statement, HAVING clause, WHERE clause

• ANY predicate condition

• SOME predicate condition

• Overview of Predicates