EQL provides existential and universal quantifiers for use with Boolean expressions against sets.
Both types of expressions can appear in any context that accepts a Boolean expression, such as SELECT
clauses, WHERE
clauses, HAVING
clauses, ORDER BY
clauses, join conditions, and so on.
Existential quantifier
An existential quantifier uses the SOME
keyword. In an existential quantifier, if any item in the set has a match based on the comparison operator that is used, the returned value is TRUE
.
SOME id IN set SATISFIES (booleanExpr)where:
The expression binds the identifier id within booleanExpr. This binding shadows any other attributes with the same name inside the predicate. Note that this shadowing applies only to references to identifiers/attributes that do not have a statement qualifier.
set
, the quantifier is TRUE.SOME
x IN
{ } SATISFIES
(x > 0) is FALSE.SOME
x IN
{ -3, -2, 1 } SATISFIES
(x > 0) is TRUE, because the predicate expression is true for x = 1.SOME
x IN
{ 5, 7, 10 } SATISFIES
(x > 0) is TRUE, because the predicate is true for x = 5.SOME
x IN
{ 'foo', '3', '4' } SATISFIES
(TO_INTEGER
(x) > 0) is TRUE, because the predicate is true for x = '3'.SOME
x IN
{ 'foo', '-1', '-2' } SATISFIES
(TO_INTEGER
(x) > 0) is NULL. The predicate is false for x = '-1' and x = '-2', but NULL for x = 'foo'.RETURN results AS SELECT WineID AS idRec, WineType AS wines, Body AS bodyAttr FROM WineState WHERE SOME x IN Body SATISFIES (x = 'Robust') ORDER BY idRec
bodyAttr idRec wines --------------------------------------------- | { Robust, Tannins } | 4 | Red | | { Robust } | 6 | Red | | { Oak, Robust | 8 | Red | | { Robust, Tannins } | 11 | Zinfandel | | { Fresh, Robust } | 19 | White | | { Firm, Robust } | 22 | Blanc de Noirs | | { Robust } | 23 | Brut | | { Robust } | 24 | Brut | | { Firm, Robust } | 25 | White | ---------------------------------------------
Only the nine records that have the Body='Robust' assignment are returned.
Universal quantifier
A universal quantifier uses the EVERY
keyword. In a universal quantifier, if every item in the set has a match based on the comparison operator that is used, the returned value is TRUE
.
EVERY id IN set SATISFIES (booleanExpr)where
id
, set
, and booleanExpr
have the same meanings as in the existential quantifier.The expression binds the identifier id within booleanExpr. This binding shadows any other attributes with the same name inside the predicate. Note that this shadowing applies only to references to identifiers/attributes that do not have a statement qualifier.
set
, the quantifier is FALSE.set
, the quantifier is TRUE.EVERY
x IN
{ } SATISFIES
(x > 0) is TRUE.EVERY
x IN
{ -3, -2, 1 } SATISFIES
(x > 0) is FALSE, because the predicate is false for x = -3.EVERY
x IN
{ 5, 7, 10 } SATISFIES
(x > 0) is TRUE, because the predicate is true for every value in the set.EVERY
x IN
{ 'foo', '3', '4' } SATISFIES
(TO_INTEGER
(x) > 0) is NULL. The predicate is true for x = '3' and x = '4', but NULL for x = 'foo'.EVERY
x IN
{ 'foo', '-1', '-2' } SATISFIES
(TO_INTEGER
(x) > 0) is FALSE, because the predicate is false for x = '-1'.RETURN results AS SELECT WineID AS idRec, WineType AS wines, Body AS bodyAttr FROM WineState WHERE (EVERY x IN Body SATISFIES (x = 'Robust')) AND (WineID IS NOT NULL) ORDER BY idRec
bodyAttr idRec wines -------------------------------- | | 1 | Chardonnay | | | 2 | Chardonnay | | { Robust } | 6 | Red | | | 17 | Merlot | | | 20 | White | | | 21 | White | | { Robust } | 23 | Brut | | { Robust } | 24 | Brut | --------------------------------
The only records that are returned are those that have only one Body='Robust' assignment (Records 6, 23, and 24) and those that have no Body assignments (Records 1, 2, 17, 20, and 21).
In the query, note the use of the "WineID IS NOT NULL" expression in the WHERE
clause. This prevents the return of other records in the system for which the universal expression would normally be evaluated as TRUE but which would return empty sets.