Quantified predicate 

By specifying a quantity predicate, you can compare a value or list of values to a set of values or value lists.

Syntax

<quantified_predicate> ::=
  <expression> <comp_op> <quantifier> <expression_list>
| <expression> <comp_op> <quantifier> <subquery>
| <expression_list> <equal_or_not> <quantifier> (<expression_list>,...)
| <expression_list> <equal_or_not> <quantifier> <subquery>

The following comparison operators are available to compare values:
<comp_op> ::= < | > | <> | != | = | <= | >=
| ~= | ~< | ~> (for computers with ASCII code)

Value lists can only be compared with the following operators:

<equal_or_not> ::= <> |=
| ~= (for computers with ASCII code)

The quantified predicate can be qualified with ALL, SOME, or ANY.

<quantifier> ::= ALL | SOME | ANY

Examples

SQL Tutorial, Subquery: Inner Queries

Explanation

The subquery (subquery) must supply a result table that contains the same number of columns as the number of values specified by the expression or expression list on the left-hand side of the operator.

Each list of values specified to the right of the equal_or_not operator expression_list must contain the same number of values as specified in the value list in front of the equal_or_not operator.

·        Let x be the result of the first expression and S the result of the subquery or sequence of values. S is a set of values s. The value x and the values in S must be comparable with each other.

·        If a value list (expression_list) is specified on the left of the comparison operator equal_or_not, then let x be the value list comprising the results of the values x₁, x₂, ..., x_n of this value list. Let S be the result of the subquery consisting of a set of value lists s or a sequence of value lists s. A value list s consists of the results of the values s₁, s₂, ..., s_n. A value xm must be comparable with all values sm.
x=s is true if xm=sm, m=1,...,n
x<>s is true if there is at least one m for which xm<>sm
x <equal or not> s is undefined if there is no m for which xm <equal or not> sm is false and if there is at least one m for which xm <equal_or_not> sm is undefined.
If one xm or one ym is a NULL value, or if the result of the subquery is empty, x <equal_or_not> y is undefined.

The entry '------' in the list below means that no statement can be made if only the result of the comparison with one s is known.

x <compare> <quantifier> S, whereby compare ::= comp_op | equal_or_not

	quantifier ::= ALL	quantifier ::= ANY | SOME
S is empty	True	false
x <compare> S is true for at least one s from S	------	true
x <compare> S is true for all s from S	True	true
x <compare> S is not false for any value from S and is undefined for at least one value s	undefined
S contains NULL values and x <compare> S is true for all other s	undefined	true
x <compare> S is false for at least one value s from S	false	------
x <compare> S is false for all s from S	false	false
x <compare> S is not true for any value s from S and is undefined for at least one value s		undefined
S contains NULL values and x <compare> S is false for all other s	false	undefined

See also:

Comparison Predicate (comparison_predicate)