Comparison Operators

operator	description
Relational Operators	Compare values
IS	Compare a value with ternary value
BETWEEN	Check if a value is with in a range of values
LIKE	Check if a string matches a pattern
IN	Check if a value is within a set of values
ANY	Check if any of values fulfill conditions
ALL	Check if all values fulfill conditions
EXISTS	Check if a subquery returns at least one row

A comparison operator returns a ternary value.

Relational Operators

operator	description
=	LHS is equal to RHS
==	LHS and RHS are of the same type, and LHS is equal to RHS
<	LHS is less than RHS
<=	LHS is less than or equal to RHS
>	LHS is greater than RHS
>=	LHS is greater than or equal to RHS
<>, !=	LHS is not equal to RHS

relational_operation
  : value operator value
  | row_value operator row_value

value: value
row_value: Row Value

Except for identical operator(“==”), at first, the relational operator attempts to convert both of operands to integer values, and if both conversions are successful then compares them. If conversions failed, next the relational operater attempts to convert the values to float, and next to datetime, boolean, at last to string.

If either of operands is null or all conversions failed, then the comparison returns UNKNOWN.

Identical operator does not perform automatic type conversion. The result will be true only when both operands are of the same type.

In case of row_values comparison, both of row_values must be tha same lengths. Values at the same indices are compared in order from left to right.

IS

value IS [NOT] NULL

value: value

Check if a value is a null value.

value IS [NOT] ternary

value: value
ternary: ternary

Evaluate the ternary value of a value and check if the ternary value is equal to ternary.

BETWEEN

between_operation
  : value [NOT] BETWEEN low AND high
  | row_value [NOT] BETWEEN row_value_low AND row_value_high

value: value
low: value
high: value
row_value: Row Value
row_value_low: Row Value
row_value_high: Row Value

Check a value is greater than or equal to low and less than or equal to high.

The BETWEEN operation is equivalent to followings.

low <= value AND value <= high
NOT (low <= value AND value <= high)

LIKE

string [NOT] LIKE pattern

string: string
pattern: string

Returns TRUE if string matches pattern, otherwise returns FALSE. If string is a null, return UNKNOWN.

In pattern, following special characters can be used.

%: any number of characters
_ (U+005F Low Line): exactly one character

IN

in_operation
  : value [NOT] IN (value [, value ...])
  | value [NOT] IN single_field_subquery
  | row_value [NOT] IN (row_value [, row_value ...])
  | row_value [NOT] IN multiple_fields_subquery

value: value
row_value: Row Value
single_field_subquery: subquery
multiple_fields_subquery: subquery

Check if value or row_value is in within the set of values or the result set of select_query.

IN is equivalent to = ANY.

NOT IN is equivalent to <> ALL.

ANY

any_operation
  : value relational_operator ANY (value [, value ...])
  | value relational_operator ANY single_field_subquery
  | row_value relational_operator ANY (row_value [, row_value ...])
  | row_value relational_operator ANY multiple_fields_subquery

value: value
row_value: Row Value
relational_operator: relational operator
single_field_subquery: subquery
multiple_fields_subquery: subquery

Compare value or row_value to each listed values or each records retrieved by select_query. If any of comparison results is TRUE, returns TRUE. If there is no TRUE result and there is at least one UNKNOWN result, returns UNKNOWN. Otherwise, returns FALSE.

If select_query returns no record, returns FALSE.

ALL

all_operation
  : value relational_operator ALL (value [, value ...])
  | value relational_operator ALL single_field_subquery
  | row_value relational_operator ALL (row_value [, row_value ...])
  | row_value relational_operator ALL multiple_fields_subquery

value: value
row_value: Row Value
relational_operator: relational operator
single_field_subquery: subquery
multiple_fields_subquery: subquery

Compare value or row_value to every listed values or each records retrieved by select_query. If any of comparison results is FALSE, returns FALSE. If there is no FALSE result and there is at least one UNKNOWN result, returns UNKNOWN. Otherwise, returns TRUE.

If select_query returns no record, returns TRUE.

Exists

EXISTS (select_query)

select_query: Select Query

Returns TRUE if a select_query returns at least one record, otherwise returns FALSE.