Two rows are compared lexicographically according to a comparison operator. If both rows only have one column, you will obtain the normal comparison of two values.
{ row comparison_op row | vector_column comparison_op expression }
row ::= { ( expression ,...) | expression | subquery }
vector_column ::= [ table .]{ column[min..max] | column ( min..max }
comparison_op ::= { = | < | > | <= | >=| <> }
row
Operands for comparison.
Each expression in row must be atomic. The row consists of the expression values in the order specified. A single expression therefore returns a row with one column.
subquery must return a table without multiple columns, and with at most one row. This row is the comparison operand. If the table returned is empty, the comparison operand is a row with the NULL value in each column.
The rows to be compared must have the same number of columns and the corresponding columns of the left and right rows must have compatible data types (see section "Compatibility between data types").
vector_column
A multiple column, which is compared according to special rules. The column specification may not be an external reference.
expression
The expression value must be atomic and its data type must be compatible with the data type of the vector_column occurrences (see section "Compatibility between data types" ).
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 |
Result
row comparison_op row
If rows with more than one column are compared , the lexicographical comparison rules for rows will apply (see section "Comparison rules".
If single-column rows are compared, the comparison rules will depend on the data type of the columns (see section "Comparison rules").
vector_column comparison_op expression
Each occurrence of vector_column is compared with expression according to the comparision rules for the data type (see section "Comparison rules" below). The comparison results are combined with OR.
Example
If X is a multiple column with 3 elements, the comparison
X[1..3] >= 13
is equivalent to the following comparisons:
X[1] >= 13 OR X[2] >= 13 OR X[3] >= 13