A comparison condition is used to compare one expression with another expression. The result of the comparison is TRUE, FALSE, or UNKNOWN. Comparison conditions can be divided into simple comparison conditions and compound comparison conditions.
Note
Comparison conditions do not support large object (LOB) data types. However, you can compare CLOB data by using a PL program.
When comparing numeric expressions, OceanBase Database determines the order of numeric comparisons in the condition based on the numeric precedence. The precedence of BINARY_DOUBLE is the highest, followed by BINARY_FLOAT, and NUMBER has the lowest precedence.
When comparing character expressions, OceanBase Database determines the order of comparisons based on the character data type comparison rules, including how the character sets of the expressions are unified before the comparison, whether to use binary or linguistic comparison, and whether to use blank padding or nonblank padding comparison semantics.
When you use a comparison condition to perform linguistic comparison on character values, the values are first converted to sort keys and then compared. The comparison process is similar to that for RAW data types. A sort key is a value returned by the NLSSORT function. If the sort keys generated by the two expressions have the same prefix, they are considered equal in linguistic comparison, even if the remaining parts of the values are different.
Two objects of non-scalar types are comparable if they have the same named type and their elements are in one-to-one correspondence. When you use a nested table, which is a user-defined object type, in an equality or IN condition, you must define a MAP method. Elements of a nested table can be compared with each other.
The following table describes comparison conditions.
Condition type |
Function |
Example |
|---|---|---|
| = | Equal | SELECT * FROM emp WHERE salary = 2000 ORDER BY empno; |
| != ^= <> | Not equal | SELECT * FROM emp WHERE salary != 2000 ORDER BY empno; |
| > < | Greater than 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 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 a value with each value in a list or with each value returned by a query. The condition must start with =, !=, >, <, <=, or >=. You can follow the condition with any expression or subquery that returns one or more values. If the query does not return any rows, the result is FALSE. | SELECT * FROM emp WHERE salary = ANY (SELECT salary FROM emp WHERE deptno = 30) ORDER BY empno; |
| ALL | Compares a value with each value in a list or with each value returned by a query. The condition must start with =, !=, >, <, <=, or >=. You can follow the condition with any expression or subquery that returns one or more values. If the query does not return any rows, the result is TRUE. | SELECT * FROM emp WHERE salary >=ALL (1900, 3000) ORDER BY empno; |
Simple comparison conditions
A simple comparison condition can be used to compare a single expression with an expression list or with the result of a subquery.
Syntax of a simple comparison condition:
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 on the left side of the operator in quantity and data type. If a single expression is compared with the result of a subquery, the values returned by the subquery must match the expression on the left side of the operator in quantity and data type.
Compound comparison conditions
A compound comparison condition can be used to compare a single expression or multiple expressions with any or all members of an expression list or the result of a subquery.
In a compound comparison condition, if a single expression or multiple expressions are compared with any or all members of an expression list, the expressions in the list must match the expressions on the left side of the operator in quantity and data type. If a single expression or multiple expressions are compared with any or all members of the result of a subquery, the values returned by the subquery must match the expressions on the left side of the operator in quantity and data type.
Syntax of compound comparison conditions:
expr {= | != | ^= | <> | < | >= | ANY | SOME | ALL } ({ expression_list | subquery})
(expr [, expr ]...){= | != | ^= | ANY | SOME | ALL} ({expression_list [, expression_list ]... |subquery})