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 ... AND ...`	`x BETWEEN a AND b` is the same as `a <= x AND x <= b`	inclusive at both endpoints
`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.