A comparison condition is used to compare two expressions. The result is TRUE, FALSE, or UNKNOWN. Comparison conditions are divided into simple comparison conditions and group comparison conditions.
Note
Comparison conditions do not support large objects (LOBs). You can use the PL program to compare CLOB data.
When comparing numeric expressions, OceanBase Database uses numeric precedence to determine which type of values the condition compares. BINARY_DOUBLE has the highest precedence, then BINARY_FLOAT, and finally NUMBER.
When comparing character expressions, OceanBase Database follows the data type comparison rules. The rules define how the character sets of the expressions are aligned before the comparison, the use of binary or linguistic comparison, and the use of blank-padded comparison semantics.
When you use a comparison condition for linguistic comparison, the expressions are first converted into sort keys before comparison, and the comparison process is similar to that for the RAW data type. Sort keys are returned by the NLSSORT function. If the sort keys generated for two expressions have the same prefix, the two expressions are linguistically equal even if the rest of their sort keys are different.
If two non-scalar objects are of the same named type and their elements are in a one-to-one correspondence, they can be compared. When you use a nested table of a user-defined object type in the equality or IN condition, you must define the MAP method. Elements in a nested table can be mutually compared.
The following table describes the comparison conditions.
| Condition type | Feature | Examples |
|---|---|---|
| = | Equality test. | SELECT * FROM emp WHERE salary = 2000 ORDER BY empno; |
| !=, ^=, <> | Not equal to | SELECT * FROM emp WHERE salary != 2000 ORDER BY empno; |
| >, < | Greater than and less than | SELECT * FROM emp WHERE salary > 2000 ORDER BY empno; SELECT * FROM emp WHERE salary < 2000 ORDER BY empno; |
| >=, <= | Greater than or equal to, and less than or equal to | SELECT * FROM emp WHERE salary >= 2000 ORDER BY empno; SELECT * FROM emp WHERE salary <=2000 ORDER BY empno; |
| ANY SOME | Compares the value to the left of ANY with each value in a list or with each value returned by a query. It must begin with =, !=, >, <, <=, or >=, and can be followed by any expression or subquery that returns one or more values. If the query does not return any rows, the calculation result is FALSE. | SELECT * FROM emp WHERE salary = ANY (SELECT salary FROM emp WHERE deptno = 30) ORDER BY empno; |
| ALL | Compares the value to the left of ALL with each value in the list or with each value returned by a query. It must begin with =, !=, >, <, <=, or >=, and can be followed by any expression or subquery that returns one or more values. If the query does not return any rows, the calculation result is TRUE. | SELECT * FROM emp WHERE salary >=ALL (1900, 3000) ORDER BY empno; |
Simple comparison conditions
A simple comparison condition specifies a comparison of a single expression with an expression list or with the results of a subquery.
Syntax for simple comparison conditions:
expr {= | != | ^= | <> | < | >= |} ( expression_list | subquery )
In a simple comparison condition, if a single expression is compared with an expression list, the expressions in the list must match the expression to the left of the operator in number and data type. If a single expression is compared with the results of a subquery, the results of the subquery must match the expression to the left of the operator in number and data type.
Group comparison conditions
A group comparison condition specifies a comparison of one or more expressions with any or all members in an expression list or the results of a subquery.
In a group comparison condition, if one or more expressions are compared with any or all members in an expression list, the expressions in the list must match the one or more expressions to the left of the operator in number and data type. If one or more expressions are compared with any or all members in the results of a subquery, the results must match the one or more expressions to the left of the operator in number and data type.
Syntaxes for group comparison conditions:
expr {= | != | ^= | <> | < | >= | ANY | SOME | ALL } ({ expression_list | subquery})
(expr [, expr ]...){= | != | ^= | ANY | SOME | ALL} ({expression_list [, expression_list ]... |subquery})