This article applies to both SQL and Notebook SQL syntax.

Functions

For the full list of functions available, see our Function List here ->


If you have requests for functions that aren't available, let us know!

Operators

Operators are represented by special characters or keywords; they do not use function call syntax. An operator manipulates any number of data inputs, also called operands, and returns a result.

Common conventions:

  • Unless otherwise specified, all operators return NULL when one of the operands is NULL.
  • All operators will throw an error if the computation result overflows.
  • For all floating-point operations, +/-inf and NaN may only be returned if one of the operands is +/-inf or NaN. In other cases, an error is returned.


Arithmetic operators

Addition: X + Y

Subtraction: X - Y

Multiplication: X * Y

Division: X / Y

Unary Minus: - X

Logical operators

  • Logical NOT: NOT X Returns FALSE if input is TRUE. Returns TRUE if input is FALSE. Returns NULL otherwise.
  • Logical AND: X AND Y: Returns FALSE if at least one input is FALSE. Returns TRUE if both X and Y are TRUE. Returns NULL otherwise.
  • Logical OR: X OR Y: Returns FALSE if both X and Y are FALSE. Returns TRUE if at least one input is TRUE. Returns NULL otherwise.

Comparison operators

  • Less Than X < Y: Returns TRUE if X is less than Y.
  • Less Than or Equal To X <= Y: Returns TRUE if X is less than or equal to Y.
  • Greater Than X > Y: Returns TRUE if X is greater than Y.
  • Greater Than or Equal To X >= Y: Returns TRUE if X is greater than or equal to Y.
  • Equal X = Y: Returns TRUE if X is equal to Y.
  • Not Equal: X != Y X <> Y: Returns TRUE if X is not equal to Y.
  • BETWEEN X [NOT] BETWEEN Y AND Z: Returns TRUE if X is [not] within the range specified. The result of "X BETWEEN Y AND Z" is equivalent to "Y <= X AND X <= Z" but X is evaluated only once in the former.
  • LIKE X [NOT] LIKE Y: Checks if the STRING in the first operand X matches a pattern specified by the second operand Y. Expressions can contain these characters:
  1. A percent sign "%" matches any number of characters or bytes
  2. An underscore "_" matches a single character or byte
  3. You can escape "\", "_", or "%" using two backslashes. For example, "\\%". If you are using raw strings, only a single backslash is required. For example, r"\%"
  • IN: Multiple - Returns FALSE if the right operand is empty. Returns NULL if the left operand is NULL. Returns TRUE or NULL, never FALSE, if the right operand contains NULL. Arguments on either side of IN are general expressions. Neither operand is required to be a literal, although using a literal on the right is most common. X is evaluated only once.

IN operators

The IN operator supports the following syntaxes:

x [NOT] IN (y, z, ... ) # Requires at least one element x [NOT] IN (<subquery>)

Arguments on either side of the IN operator are general expressions. It is common to use literals on the right-side expression; however, this is not required.

IS operators

IS operators return TRUE or FALSE for the condition they are testing. They never return NULL, even for NULL inputs, unlike the IS_INF and IS_NAN functions defined in Mathematical Functions. If NOT is present, the output BOOL value is inverted.


Examples:

X IS [NOT] NULL
X IS [NOT] TRUE
X IS [NOT] FALSE

Concatenation operator

The concatenation operator combines multiple values into one.

STRING || STRING [ || ... ]
Did this answer your question?