InterSystems SQL Reference
ALL
|
|
Matches a value with all corresponding values from a subquery.
Synopsis
scalar-expression comparison-operator ALL (subquery)
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.
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
Content Date/Time: 2019-02-18 01:15:52