Skip to content

comparisons.md

Latest commit

 

History

History
154 lines (149 loc) · 5.79 KB

comparisons.md

File metadata and controls

154 lines (149 loc) · 5.79 KB

Comparison Operations

The following operations can take operands with multiple data types but always return a Boolean value (sometimes nullable):

Comparison Operations
Operation Definition Observation
= equality test
<> inequality test
!= inequality test, same as above
> greater than
< less than
>= greater or equal
<= less or equal
IS NULL true if operand is NULL
IS NOT NULL true if operand is not NULL
<=> equality check that treats NULL values as equal result is not nullable
IS DISTINCT FROM check if two values are not equal, treating NULL as equal result is not nullable
IS NOT DISTINCT FROM check if two values are the same, treating NULL values as equal result is not nullable
BETWEEN [ASYMMETRIC] ... AND ... x BETWEEN a AND b is the same as a <= x AND x <= b inclusive at both endpoints
BETWEEN SYMMETRIC ... AND ... x BETWEEN a AND b is the same as (a <= x AND x <= b) OR (b <= x AND x <= a) inclusive at both endpoints; order of endpoints does not matter
NOT BETWEEN ... AND ... The NOT of the previous operator not inclusive at either endpoint
... [NOT] IN ... checks whether value appears/does not appear in a list or set
EXISTS query check whether query results have at least one row

Note that the SQL standard mandates IS NULL to return true for a ROW object where all fields are NULL (similarly, IS NOT NULL is required to return false). Our compiler diverges from the standard, returning false for ROW(null) is null.

Comparison operations (=, <>, !=, <, >, <=, >=, <=>, IS NULL, IS NOT NULL) are defined on all data types, even generic and recursive data types (including ARRAY, MAP, ROW, VARIANT, user-defined types). For complex types, comparisons are performed lexicographically on the type structure. In such comparisons fields with NULL values are compared smaller than any other value.

Other conditional operators

CASE value WHEN value1 [, value11 ]* THEN result1 [ WHEN valueN [, valueN1 ]* THEN resultN ]* [ ELSE resultZ ] END Simple case expression: returns the result corresponding to the first valueN that matches value.
CASE WHEN condition1 THEN result1 [ WHEN conditionN THEN resultN ]* [ ELSE resultZ ] END Searched case: returns result corresponding to first condition that evaluates to 'true'.
COALESCE(value0, value1 [, valueN ]*) Returns the first non-null value. For example, COALESCE(NULL, 5) returns 5.
GREATEST( expr [, expr ]* ) The largest of a number of expressions; if any argument is NULL, the result is NULL.
GREATEST_IGNORE_NULLS( expr [, expr ]* ) The largest of a number of expressions; only if all arguments are NULL, the result is NULL; otherwise NULL values are ignored.
IF( condition, ifTrue, ifFalse ) Returns ifTrue if the condition evaluates to 'true', returns ifFalse otherwise.
IFNULL( left, right ) Equivalent to COALESCE(left, right).
LEAST( expr [, expr ]* ) The smallest of a number of expressions; if any argument is NULL, the result is NULL.
LEAST_IGNORE_NULLS( expr [, expr ]* ) The smallest of a number of expressions; only if all arguments are NULL, the result is NULL; otherwise NULL values are ignored.
NULLIF(value0, value1) Returns `NULL` if the value0 and value1 are the same. For example, NULLIF(5, 5) returns NULL; NULLIF(5, 0) returns 5.