All operators
Operators by type
Mathematical Operators
| Operator | Description | Example | Result | Function |
|---|
| + | Addition | 5 + 3 | 8 | ADD |
| - | Subtraction | 7 - 2 | 5 | SUBSTRACT |
| * | Multiplication | 4 * 3 | 12 | MULTIPLY |
| / | Division | 7 / 2 | 3.5 | DIVIDE |
| ^ | Power | 2 ^ 3 | 8 | POW |
| // | Integer division | 7 // 3 | 2 | DIVIDE_TYPE |
| % | Modulus (remainder) | 7 % 3 | 1 | MOD |
See also: Math Functions
Logical Operators
| Operator | Description | Example | Result | Function |
|---|
| AND | Logical AND | age > 18 AND salary > 50000 | TRUE if both conditions are true | AND |
| OR | Logical OR | status = 'active' OR status = 'pending' | TRUE if either condition is true | OR |
| NOT | Logical NOT | NOT is_deleted | TRUE if is_deleted is false | NOT |
See also: Logical Functions
Comparison Operators
| Operator | Description | Example | Result | Function |
|---|
| < | Less than | price < 100 | TRUE if price is less than 100 | LESS_THAN |
| > | Greater than | age > 18 | TRUE if age is greater than 18 | GREATER_THAN |
| <= | Less than or equal to | quantity <= 5 | TRUE if quantity is 5 or less | LESS_THAN_OR_EQUALS |
| >= | Greater than or equal to | score >= 90 | TRUE if score is 90 or greater | GREATER_THAN_OR_EQUALS |
| = | Equal to | status = 'active' | TRUE if status equals 'active' | EQUALS |
| <> or != | Not equal to | color <> 'red' | TRUE if color is not 'red' | NOT_EQUALS |
| BETWEEN | Range check | age BETWEEN 18 AND 65 | TRUE if age is 18-65 inclusive | BETWEEN |
See also: Comparison Functions
String Operators
| Operator | Description | Example | Result | Function |
|---|
| || | String concatenation | 'Hello' || ' ' || 'World' | 'Hello World' | CONCAT_FUNC |
| LIKE | Pattern matching | name LIKE 'Jo%' | TRUE if name starts with 'Jo' | LIKE |
See also: String Functions
Set Operators
| Operator | Description | Example | Result | Function |
|---|
| IN | Membership check | value IN (1, 2, 3) | TRUE if value is in the list | IN |
See also: Comparison Functions
Special Operators
| Operator | Description | Example | Result | Function |
|---|
| IS NULL | NULL check | phone IS NULL | TRUE if phone is NULL | IS_NULL |
| IS NOT NULL | Non-NULL check | email IS NOT NULL | TRUE if email is not NULL | IS_NOT_NULL |
| [] | Array/Row extraction | array[1] or row[field_1] | Returns element at index 1 or field_1 | ARRAY_EXTRACT |
See also: Comparison Functions , Array Functions
Operators with unbounded arguments
| Operator | Description | Example | Result | Function |
|---|
| FLATTEN | Flattens an array of arrays into a single array | FLATTEN(ARRAY(1, 2), ARRAY(3, 4)) | ARRAY(1, 2, 3, 4) | FLATTEN_FUNC |
| MERGE | Merges multiple arrays into a single array | MERGE(ARRAY(1, 2), ARRAY(3, 4)) | ARRAY(1, 2, 3, 4) | MERGE_FUNC |
| ARRAY_MERGE | Merges multiple arrays into a single array | ARRAY_MERGE(ARRAY(1, 2), ARRAY(3, 4)) | ARRAY(1, 2, 3, 4) | ARRAY_MERGE_FUNC |
| ZIP | Zips multiple arrays into a single array | ZIP(ARRAY(1, 2), ARRAY(3, 4)) | ARRAY(1, 3, 2, 4) | ZIP_FUNC |
| ARRAY_CONCAT | Concatenates multiple arrays into a single array | ARRAY_CONCAT(ARRAY(1, 2), ARRAY(3, 4)) | ARRAY(1, 2, 3, 4) | ARRAY_CONCAT_FUNC |
| ALL | Returns TRUE if all values in the array are TRUE | ALL(TRUE, TRUE, TRUE) | TRUE | ALL_FUNC |
| ANY | Returns TRUE if any value in the array is TRUE | ANY(TRUE, FALSE, TRUE) | TRUE | ANY_FUNC |
| NONE | Returns TRUE if no value in the array is TRUE | NONE(FALSE, FALSE, FALSE) | TRUE | NONE_FUNC |
| GREATEST | Returns the greatest value in the array | GREATEST(1, 2, 3) | 3 | GREATEST_FUNC |
| LEAST | Returns the least value in the array | LEAST(1, 2, 3) | 1 | LEAST_FUNC |
| COALESCE | Returns the first non-NULL value in the array | COALESCE(NULL, 2, NULL) | 2 | COALESCE_FUNC |
| CONCAT | Concatenates multiple strings into a single string | CONCAT('Hello', 'World') | 'HelloWorld' | CONCAT_FUNC |
| CONCAT_WS | Concatenates multiple strings with a separator | CONCAT_WS('-', 'Hello', 'World') | 'Hello-World' | CONCAT_WS_FUNC |
Operators precedence
| Operator | Precedence | Description |
|---|
| () | 1 | Parentheses |
| := | 2 | Assignment |
| [] | 3 | Array/Row extraction |
| :: | 4 | Type casting |
| . | 5 | Function chaining |
| * / // % ^ | 5 | Multiplication/division/integer division/modulus/power |
| + - | 6 | Addition/subtraction |
| = <> != > >= < <= | 7 | Comparison |
| AND OR | 8 | Logical AND/OR |