7.19.8 Quantified predicate
Quantified predicates are used to compare the result of a value expression to the result of a table subquery.
- Organization of this subsection
(1) Specification format
quantified-predicate ::= value-expression{=|<>|!=|^=|<|<=|>|>=}{{ANY|SOME}|ALL}table-subquery
(2) Explanation of specification format
- value-expression:
-
Specifies, in the form of a value expression, the column to be evaluated by the quantified predicate. For details about value expressions, see 7.20 Value expression.
- ANY or SOME:
-
If there is at least one row in the results from the table subquery that satisfies the comparison with value-expression, the result of the quantified predicate is TRUE.
The results are the same regardless of whether you use ANY or SOME.
- ALL:
-
If either of the following conditions is met, the result of the quantified predicate is TRUE:
-
If all the rows in the results of the table subquery satisfy the comparison with value-expression
-
If the result of the table subquery is zero rows (the empty set)
-
- table-subquery:
-
For details about table subqueries, see 7.3 Subqueries.
(3) Evaluation of the predicate
(a) When ANY or SOME is specified
-
If there is at least one row in the results from the table subquery that satisfies the comparison with value-expression, the result of the quantified predicate is TRUE.
-
If either of the following conditions is met, the result of the quantified predicate is FALSE:
-
If all of the rows in the results of the table subquery fail to satisfy the comparison with value-expression
-
If the result of the table subquery is zero rows (the empty set)
-
-
Otherwise, the result is unknown.
The following table shows the result of a quantified predicate in which ANY or SOME is specified.
|
No. |
Results of comparison to every row in the table subquery |
Result of the quantified predicate |
|
|---|---|---|---|
|
1 |
Some TRUE rows |
TRUE |
|
|
2 |
No TRUE rows |
Some undefined |
Undefined |
|
3 |
No undefined |
FALSE |
|
|
4 |
0 rows (the empty set) |
FALSE |
|
(b) When ALL is specified
-
If either of the following conditions is met, the result of the quantified predicate is TRUE:
-
If all the rows in the results of the table subquery satisfy the comparison with value-expression
-
If the result of the table subquery is zero rows (the empty set)
-
-
If any of the rows in the results of the table subquery fail to satisfy the comparison with value-expression, the result of the quantified predicate is FALSE.
-
Otherwise, the result is undefined.
The following table shows the result of a quantified predicate in which ALL is specified.
|
No. |
Results of comparison to every row in the table subquery |
Result of the quantified predicate |
|
|---|---|---|---|
|
1 |
Some FALSE rows |
FALSE |
|
|
2 |
No FALSE rows |
Some undefined |
Undefined |
|
3 |
No undefined |
TRUE |
|
|
4 |
0 rows (the empty set) |
TRUE |
|
(4) Rules
-
There must be exactly one column in the results of the table subquery.
-
When binary data is specified in value-expression, the only comparison operators allowed are =, <>, !=, and ^=.
(5) Notes
-
When you specify a quantified predicate, a work table might be created. If the size of the work table DB area where the work table is created has not been estimated correctly, it might result in performance degradation. For details about estimating the size of the work table DB area, see the HADB Setup and Operation Guide. For details about work tables, see Considerations when executing an SQL statement that creates work tables in the HADB Application Development Guide.
-
If a quantified predicate (=ANY or =SOME specification) is specified, HADB performs deduplication of the table subquery results.
(6) Example
- Example
-
From the customer table (USERSLIST) and sales history table (SALESLIST), this example retrieves information on customers who purchased product code (PUR-CODE) P001.
SELECT * FROM "USERSLIST" WHERE "USERID"=ANY(SELECT "USERID" FROM "SALESLIST" WHERE "PUR-CODE"='P001')The underlined portion indicates the quantified predicate.