Quantified Predicate (quantified_predicate)

By specifying a quantified predicate (quantified_predicate), you can compare a value or list of values to a set of values or value lists.

Structure

Syntax 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>

End of the code.

The following operators are available for comparing values:

Syntax Syntax

<comp_op> ::=
  <
| >
| <>
| !=
| =
| <=
| >=
| ~= <!  for computers with ASCII code  !>
| ~< <!  for computers with ASCII code  !>
| ~> <!  for computers with ASCII code  !>

End of the code.

Only the following operators are available for comparing value lists:

Syntax Syntax

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

End of the code.

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

Syntax Syntax

```
<quantifier> ::=
  ALL
| SOME
| ANY
```

End of the code.

Examples

SQL Tutorial, Subquery: Inner Queries

Explanation

The subquery must supply a result table that contains the same number of columns as the number of values created by the expression (expression or expression_list) on the left of the operator.

Each list of values (expression_list) specified to the right of the equal_or_not comparison operator 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 value expression and S the result of the subquery or sequence of values. S is a set of s values. 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 x1, x2, ..., xn 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 s1, s2, ..., sn.

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 sm is a NULL value, or if the result of the subquery is empty, then 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 s 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 true for all other s	False	Undefined

More Information

Comparison Predicate (comparison_predicate)