The value of a row is compared with the rows of a table. It is determined whether the comparison holds true either for all the rows of the table, or else for at least one row.
row comparison_op { ALL | SOME | ANY } subquery_1
row ::= { (expression ,...) | expression | subquery_2 }
comparison_op ::= { = | < | > | <= | >=| <> }
row
Left operand for the comparison.
Each expression in row must be an atomic value. The row consists of the expression values in the order specified. A single expression therefore returns a row with one column.
subquery_2
must return a table without multiple columns and with at most one row. This row is the left comparison operand. If the table returned is empty, the comparison operand is a row with the NULL value in each column.
comparison_op
Comparison operator.
= | Compare whether two values are the same |
< | Compare whether one value is smaller than the other |
> | Compare whether one value is greater than the other |
<= | Compare whether one value is smaller than or equal to the other |
>= | Compare whether one value is greater than or equal to the other |
<> | Compare whether two values are not equal |
subquery_1
The number of columns must equal the number of columns of row; corresponding columns of row and subquery_1 must have compatible data types (see section "Compatibility between data types").
Result
ALL
True if the right-hand operand is an empty table or if the results of the comparisons of the left-hand operand with each row of the right-hand operand are all true.
False if the result of the comparison of the left-hand operand with at least one row of the right-hand operand is false.
Unknown in all other cases.
SOME / ANY
True if the result of the comparison of the left-hand operand with at least one row of the right-hand operand is true.
False if the right-hand operand is an empty table or if the results of the comparisons of the left-hand operand with each row of the right-hand operand are all false.
Unknown in all other cases.
All comparisons are carried out according to the comparison rules in section "Comparison rules" on "Comparison rules".
Examples
This returns true if the current date is later than all the dates in the derived column and all of these dates are non-null. It returns false if the current date is earlier than at least one date or is the same as at least one date other than NULL in the derived column. In all other cases, the comparison returns unknown.
CURRENT_DATE > ALL (SELECT target FROM orders)
From the PURPOSE table, select the items that have a part the total number of which is greater than the total number of all the parts of the item with the item number 1.
SELECT item_num FROM purpose
WHERE number > ALL (SELECT number FROM purpose WHERE item_num = 1)