---
url: /mosaic/api/sql/operators.md
---
# Operators

SQL comparison operator expressions.

## and

`and(...clauses)`

Returns an expression for the logical `AND` of the provided *clauses*.
The *clauses* array will be flattened and any `null` entries will be ignored.

## or

`or(...clauses)`

Returns an expression for the logical `OR` of the provided *clauses*.
The *clauses* array will be flattened and any `null` entries will be ignored.

## not

`not(expression)`

Returns an expression for the logical `NOT` of the provided *expression*.

## eq

`eq(a, b)`

Returns an expression testing if expression *a* is equal to expression *b*.
In SQL semantics, two `NULL` values are not considered equal.
Use `isNotDistinct` to compare values with `NULL` equality.

## neq

`neq(a, b)`

Returns an expression testing if expression *a* is not equal to expression *b*.
In SQL semantics, two `NULL` values are not considered equal.
Use `isDistinct` to compare values with `NULL` equality.

## lt

`lt(a, b)`

Returns an expression testing if expression *a* is less than expression *b*.

## gt

`gt(a, b)`

Returns an expression testing if expression *a* is greater than expression *b*.

## lte

`lte(a, b)`

Returns an expression testing if expression *a* is less than or equal to expression *b*.

## gte

`gte(a, b)`

Returns an expression testing if expression *a* is greater than or equal to expression *b*.

## isNull

`isNull(expression)`

Returns an expression testing if the input *expression* is a `NULL` value.

## isNotNull

`isNotNull(expression)`

Returns an expression testing if the input *expression* is not a `NULL` value.

## isDistinct

`isDistinct(a, b)`

Returns an expression testing if expression *a* is distinct from expression *b*.
Unlike normal SQL equality checks, here `NULL` values are not considered distinct.

## isNotDistinct

Returns an expression testing if expression *a* is not distinct from expression *b*.
Unlike normal SQL equality checks, here `NULL` values are not considered distinct.

## isBetween

`isBetween(expression, [lo, hi])`

Returns an expression testing if the input *expression* lies between the values *lo* and *hi*, provided as a two-element array.
Equivalent to `lo <= expression AND expression <= hi`.

## isNotBetween

`isNotBetween(expression, [lo, hi])`

Returns an expression testing if the input *expression* does not lie between the values *lo* and *hi*, provided as a two-element array.
Equivalent to `NOT(lo <= expression AND expression <= hi)`.

## isIn

`isIn(expression, values)`

Returns an expression testing if the input *expression* matches any of the entries in the *values* array. Maps to `expression IN (...values)`.

## compareAny

`compareAny(expression, op, subquery)`

Returns an expression testing if the comparison *op* holds between the input *expression* and any row of the single-column *subquery*.
The *op* argument is one of `"="`, `"<>"`, `"!="`, `"<"`, `">"`, `"<="`, or `">="`.
Maps to `expression op ANY (subquery)`.

## compareAll

`compareAll(expression, op, subquery)`

Returns an expression testing if the comparison *op* holds between the input *expression* and every row of the single-column *subquery*.
The *op* argument is one of `"="`, `"<>"`, `"!="`, `"<"`, `">"`, `"<="`, or `">="`.
Maps to `expression op ALL (subquery)`.

## listContains

`listContains(expression, value)`

Returns an expression testing if the input *value* exists in the *expression* list. Maps to `list_contains(expression, value)`.

## listHasAny

`listHasAny(expression, values)`

Returns an expression testing if any of the input *values* exist in the *expression* list. Maps to `list_has_any(expression, values)`.

## listHasAll

`listHasAll(expression, values)`

Returns an expression testing if all the input *values* exist in the *expression* list. Maps to `list_has_all(expression, values)`.

## subscript

`subscript(expression, index)`

Returns an expression that indexes into the *expression* list, string, struct, or map. Maps to `expression[index]`.
String-valued *index* arguments are interpreted as literals, for example to access struct fields; use `column` to index by a column value.

## slice

`slice(expression, start, end, step)`

Returns an expression that slices the *expression* list or string. Maps to `expression[start:end:step]`.
The *start* and *end* indices are inclusive and 1-based. If *start* or *end* is `null` or `undefined`, the slice extends to the beginning or end of the input.
The *step* argument is optional.

## unnest

`unnest(expression)`

Returns an expression that unnests the *expression* list or struct. Maps to `UNNEST(expression)`.