Comparison Functions

Updated on 2024-04-19 GMT+08:00

View PDF

**Table 1** Comparison functions
SQL Function	Return Type	Description
value1 = value2	BOOLEAN	Returns TRUE if value1 equals value2; returns UNKNOWN if either value1 or value2 is NULL.
value1 <> value2	BOOLEAN	Returns TRUE if value1 does not equal value2; returns UNKNOWN if either value1 or value2 is NULL.
value1 > value2	BOOLEAN	Returns TRUE if value1 is greater than value2; returns UNKNOWN if either value1 or value2 is NULL.
value1 >= value2	BOOLEAN	Returns TRUE if value1 is greater than or equal to value2; returns UNKNOWN if either value1 or value2 is NULL.
value1 < value2	BOOLEAN	Returns TRUE if value1 is less than value2; returns UNKNOWN if either value1 or value2 is NULL.
value1 <= value2	BOOLEAN	Returns TRUE if value1 is less than or equal to value2; returns UNKNOWN if either value1 or value2 is NULL.
value IS NULL	BOOLEAN	Returns TRUE if value is NULL.
value IS NOT NULL	BOOLEAN	Returns TRUE if value is not NULL.
value1 IS DISTINCT FROM value2	BOOLEAN	Returns TRUE if value1 and value2 have different data types or values; returns FALSE if they have the same data types and values. Treats NULL as the same. For example: 1 IS DISTINCT FROM NULL returns TRUE; NULL IS DISTINCT FROM NULL returns FALSE.
value1 IS NOT DISTINCT FROM value2	BOOLEAN	Returns TRUE if they have the same data types and values; returns FALSE if value1 and value2 have different data types or values. Treats NULL as the same. For example: 1 IS NOT DISTINCT FROM NULL returns FALSE; NULL IS NOT DISTINCT FROM NULL returns TRUE.
value1 BETWEEN [ ASYMMETRIC \| SYMMETRIC ] value2 AND value3	BOOLEAN	Returns TRUE if value1 is greater than or equal to value2 and less than or equal to value3, using the default or ASYMMETRIC keyword. If SYMMETRIC is used, returns TRUE if value1 is inclusively between value2 and value3. Returns FALSE or UNKNOWN if value2 or value3 is NULL. For example: 12 BETWEEN 15 AND 12 returns FALSE; 12 BETWEEN SYMMETRIC 15 AND 12 returns TRUE; 12 BETWEEN 10 AND NULL returns UNKNOWN; 12 BETWEEN NULL AND 10 returns FALSE; 12 BETWEEN SYMMETRIC NULL AND 12 returns UNKNOWN.
value1 NOT BETWEEN [ ASYMMETRIC \| SYMMETRIC ] value2 AND value3	BOOLEAN	Returns TRUE if value1 is less than value2 or greater than value3, using the default or ASYMMETRIC keyword. If SYMMETRIC is used, returns TRUE if value1 is not between value2 and value3. Returns TRUE or UNKNOWN if value2 or value3 is NULL. For example: 12 NOT BETWEEN 15 AND 12 returns TRUE; 12 NOT BETWEEN SYMMETRIC 15 AND 12 returns FALSE; 12 NOT BETWEEN NULL AND 15 returns UNKNOWN; 12 NOT BETWEEN 15 AND NULL returns TRUE; 12 NOT BETWEEN SYMMETRIC 12 AND NULL returns UNKNOWN.
string1 LIKE string2 [ ESCAPE char ]	BOOLEAN	Returns TRUE if string1 matches string2; returns UNKNOWN if either string1 or string2 is NULL. Escape characters can be defined if needed, but they are not currently supported.
string1 NOT LIKE string2 [ ESCAPE char ]	BOOLEAN	Returns TRUE if string1 does not match string2; returns UNKNOWN if either string1 or string2 is NULL. Escape characters can be defined if needed, but they are not currently supported.
string1 SIMILAR TO string2 [ ESCAPE char ]	BOOLEAN	Returns TRUE if string1 matches the SQL regular expression string2; returns UNKNOWN if either string1 or string2 is NULL. Escape characters can be defined if needed, but they are not currently supported.
string1 NOT SIMILAR TO string2 [ ESCAPE char ]	BOOLEAN	Returns TRUE if string1 does not match the SQL regular expression string2; returns UNKNOWN if either string1 or string2 is NULL. Escape characters can be defined if needed, but they are not currently supported.
value1 IN (value2 [, value3]* )	BOOLEAN	Returns TRUE if value1 exists in the given list (value2, value3, …); returns TRUE if the list contains NULL and value1 can be found, otherwise returns UNKNOWN. Always returns UNKNOWN if value1 is NULL. For example: 4 IN (1, 2, 3) returns FALSE; 1 IN (1, 2, NULL) returns TRUE; 4 IN (1, 2, NULL) returns UNKNOWN.
value1 NOT IN (value2 [, value3]* )	BOOLEAN	Returns TRUE if value1 does not exist in the given list (value2, value3, …); returns FALSE if the list contains NULL and value1 can be found, otherwise returns UNKNOWN. Always returns UNKNOWN if value1 is NULL. For example: 4 NOT IN (1, 2, 3) returns TRUE; 1 NOT IN (1, 2, NULL) returns FALSE; 4 NOT IN (1, 2, NULL) returns UNKNOWN.
EXISTS (sub-query)	BOOLEAN	Returns TRUE if the subquery returns at least one row. Only operations that can be overridden in join and grouping operations are supported. For streaming queries, this operation is rewritten in joins and grouping. The calculation of the query result required state may increase indefinitely based on the number of input rows. Provide a query configuration with effective retention intervals to prevent excessive state.
value IN (sub-query)	BOOLEAN	Returns TRUE if value is equal to one row in the subquery result set.
value NOT IN (sub-query)	BOOLEAN	Returns TRUE if value is not contained in the rows returned by the subquery.