All Products
Search
Document Center

Comparison conditions

Last Updated: Jun 18, 2021

Comparison conditions are used to compare an expression with another expression. The result of the comparison is TRUE, FALSE, or UNKNOWN.

You cannot use comparison conditions to compare data of large object (LOB) data types. However, you can compare CLOB data by using PL programs.

When numeric expressions are compared, ApsaraDB for OceanBase uses numeric precedence to determine the order in which the numeric values in the conditions are compared. For example, the numeric values of different types appear on both sides of a comparison operator. In this case, if one of the types is BINARY_DOUBLE, BINARY_DOUBLE values are compared first. Then, BINARY_FLOAT values are compared, and finally NUMBER values are compared. ApsaraDB for OceanBase determines whether to compare NUMBER, BINARY_FLOAT, or BINARY_DOUBLE values.

When character expressions are compared, ApsaraDB for OceanBase uses specifications in comparison rules of character data types. The rules specify how the character sets of expressions are unified before they are compared, by binary and linguistic comparison, or by blank-padded and nonpadded comparison semantics.

When comparison conditions are used to perform a linguistic comparison on character values, the character values are first converted to sort keys and then compared. The comparison process is similar to that of the RAW data type. The sort keys are the values that are returned by the NLSSORT function. If the sort keys that are generated by two expressions have the same prefix, the expressions can be linguistically equal even if they differ in the rest of the value.

If two objects of the non-scalar type are of the same named type and a one-to-one correspondence exists between their elements, the two objects are comparable. When you use user-defined object types (nested tables) in equality or IN conditions, you must define the MAP method. The elements of the nested tables are comparable.

Simple comparison conditions

A simple comparison condition can compare a single expression with an expression list, or compare a single expression with the results of a subquery.

Simple comparison conditions have the following syntax:

expr   {= | ! = | ^= | <> | < | >= |}   ( expression_list | subquery )

Assume that a single expression is compared with an expression list in a simple comparison condition. The number and data type of the expressions in the expression list must match those of the expressions to the left of the operator. Assume that a single expression is compared with the results of a subquery. The number and data type of the values that are returned by the subquery must match those of the expressions to the left of the operator.

Group comparison conditions

A group comparison condition can compare a single expression with a member or all the members of expression lists or the results of a subquery. The group comparison condition can also compare multiple expressions with a member or all the members of expression lists or the results of a subquery.

Assume that a single expression or multiple expressions are compared with a member or all the members of expression lists in a group comparison condition. The number and data type of the expressions in each expression list must match those of the expressions to the left of the operator. Assume that a single expression or multiple expressions are compared with a member or all the members of the results of a subquery. The number and data type of the values that are returned by the subquery must match those of the expressions to the left of the operator.

Group comparison conditions have the following two types of syntax:

expr  {= | ! = | ^= | <> | < | >= | ANY | SOME | ALL } ({ expression_list | subquery})
(expr [, expr ]...){ = | ! = | ^= | ANY | SOME | ALL} ({expression_list  [, expression_list ]... |subquery})