Comparison Functions

between(x, min, max) boolean

Returns true if x is within the specified [min, max] range inclusive. The types of all arguments must be the same. Supported types are: TINYINT, SMALLINT, INTEGER, BIGINT, DOUBLE, REAL.

equalnullsafe(x, y) boolean

Returns true if x is equal to y. Supports all scalar types. The types of x and y must be the same. Unlike equalto() returns true if both inputs are NULL and false if one of the inputs is NULL. Corresponds to Spark’s operator <=>.

equalto(x, y) boolean

Returns true if x is equal to y. Supports all scalar types. The types of x and y must be the same. Corresponds to Spark’s operators = and ==.

greaterthan(x, y) boolean

Returns true if x is greater than y. Supports all scalar types. The types of x and y must be the same. Corresponds to Spark’s operator >.

greaterthanorequal(x, y) boolean

Returns true if x is greater than y or x is equal to y. Supports all scalar types. The types of x and y must be the same. Corresponds to Spark’s operator >=.

greatest(value1, value2, ..., valueN) [same as input]

Returns the largest of the provided values ignoring nulls. Supports all scalar types. The types of all arguments must be the same.

SELECT greatest(10, 9, 2, 4, 3); -- 10
SELECT greatest(10, 9, 2, 4, 3, null); -- 10
SELECT greatest(null, null) - null
isnotnull(x) boolean

Returns true if x is not null, or false otherwise.

SELECT isnotnull(1); -- true
isnull(x) boolean

Returns true if x is null, or false otherwise.

SELECT isnull(1); -- false
least(value1, value2, ..., valueN) [same as input]

Returns the smallest of the provided values ignoring nulls. Supports all scalar types. The types of all arguments must be the same.

SELECT least(10, 9, 2, 4, 3); -- 2
SELECT least(10, 9, 2, 4, 3, null); -- 2
SELECT least(null, null) - null
lessthan(x, y) boolean

Returns true if x is less than y. Supports all scalar types. The types of x and y must be the same. Corresponds to Spark’s operator <.

lessthanorequal(x, y) boolean

Returns true if x is less than y or x is equal to y. Supports all scalar types. The types of x and y must be the same. Corresponds to Spark’s operator <=.

notequalto(x, y) boolean

Returns true if x is not equal to y. Supports all scalar types. The types of x and y must be the same. Corresponds to Spark’s operator !=.