> ## Documentation Index > Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt > Use this file to discover all available pages before exploring further. ## Submitting Feedback If you encounter incorrect, outdated, or confusing documentation on this page, submit feedback: POST https://docs.firebolt.io/feedback ```json { "path": "/reference-sql/lexical-structure/operators", "feedback": "Description of the issue" } ``` Only submit feedback when you have something specific and actionable to report. > Reference for SQL operators available in Firebolt. # Operators Operators are represented by special characters or keywords, they do not use function call syntax. ## Operator Precedence The following table lists all SQL operators from highest to lowest precedence, alongside with their associativity. The precedence and associativity of the operators is hard-coded into the parser. Add parentheses if you want an expression with multiple operators to be interpreted in some other way than what the precedence rules imply. | Operator | Description | Associativity | | -------------------------- | ---------------------------------------- | ------------- | | `.` | Table/column name separator | left | | `::` | Type cast | left | | `[]` | Array element | left | | `+` `-` | Unary plus and minus | right | | `AT` | `AT TIME ZONE` | left | | `^` | exponentiation | left | | `*` `/` `%` | multiplication, division, modulo | left | | `+` `/` | addition, subtraction | left | | `\|\|` | concatenation | left | | `BETWEEN` | range containment | | | `LIKE` `ILIKE` | string matching | | | `<` `>` `=` `<=` `>=` `<>` | comparison | | | `IS` | `IS NULL`, `IS DISTINCT FROM`, `IS TRUE` | | | `NOT` | logical negation | right | | `AND` | logical conjunction | left | | `OR` | logical disjunction | left | ## :: Type cast Use can use the `::` operator instead of the [CAST](/reference-sql/functions-reference/conditional-and-miscellaneous/cast) function to convert one [data type](/reference-sql/data-types) to another. ### Syntax ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} -- CAST function CAST( AS ) -- :: operator :: ``` | Component | Description | | :-------- | :----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `` | The value to convert or an expression that results in a value to convert. Can be a column name, a function applied to a column or another function, or a literal value. | | `` | The target [data type](/reference-sql/data-types) (case-insensitive). | ### Example ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} SELECT '2021-12-31'::DATE; SELECT 8.5::REAL; SELECT col_a::BIGINT; ``` ## Arithmetic with numbers | Operator | Operator description | Input Data Types | Output Data Types | Example | Result | | :------- | :----------------------------------------------- | :----------------------------------------------- | :----------------- | :---------------- | :----- | | + | addition | INTEGER, BIGINT, NUMERIC, REAL, DOUBLE PRECISION | Corresponding type | `SELECT 2 + 3;` | 5 | | - | subtraction | INTEGER, BIGINT, NUMERIC, REAL, DOUBLE PRECISION | Corresponding type | `SELECT 2 - 3;` | -1 | | \* | multiplication | INTEGER, BIGINT, NUMERIC, REAL, DOUBLE PRECISION | Corresponding type | `SELECT 2 * 3;` | 6 | | / | division (integer division truncates the result) | INTEGER, BIGINT, NUMERIC, REAL, DOUBLE PRECISION | Corresponding type | `SELECT 4 / 2;` | 2 | | % | modulo (remainder) | INTEGER, BIGINT | Corresponding type | `SELECT 5 % 4;` | 1 | | ^ | exponentiation | INTEGER, BIGINT, NUMERIC, REAL, DOUBLE PRECISION | DOUBLE PRECISION | `SELECT 2 ^ 3.0;` | 8 | In arithmetic operations like +, -, \*, and / , the result's data type aligns with the most encompassing type of the operands indicated as "Corresponding type" in the table above. For clarity: * When both operands are of the same data type (e.g., two INTEGERs or two NUMERICs), the result will also be of that same data type. * `INTEGER INTEGER = INTEGER` * `INTEGER BIGINT = BIGINT` * For operations involving two different numeric data types, the result will typically be of the more precise or larger data type. * `INTEGER REAL = DOUBLE PRECISION` * Overflow checks and floating point errors are applied according to the result data type only. **Floating point precision** means that the representation of a number is accurate up to a certain number of digits. In Firebolt, `REAL` data types have 6-digit precision and `DOUBLE PRECISION` have 16-digit precision. This means that calculations have a precision of 6 or 16 respectively, and numbers are truncated to that precision. For example, if a number is stored as 1.234567, it is automatically truncated to 1.23456 for `REAL`.

When performing arithmetic, the number of leading digits in the output is the product of the leading digits in both inputs. This means that if either or both of the input numbers are larger than 6, those numbers are the first truncated, and then the arithmetic is performed. ## Arithmetic with date/time and intervals An `interval` represents a duration. In Firebolt, values of type `interval` can be used to add or subtract a duration to/from a date or timestamp. `Interval` cannot be used as the data type of a column. | Operator | Description | | :---------------------------------------- | :------------------------------------------ | | `DATE + interval -> TIMESTAMP` | Add an `interval` to a `DATE` | | `DATE - interval -> TIMESTAMP` | Subtract an `interval` from a `DATE` | | `TIMESTAMP + interval -> TIMESTAMP` | Add an `interval` to a `TIMESTAMP` | | `TIMESTAMP - interval -> TIMESTAMP` | Subtract an `interval` from a `TIMESTAMP` | | `TIMESTAMPTZ + interval -> TIMESTAMPTZ` | Add an `interval` to a `TIMESTAMPTZ` | | `TIMESTAMPTZ - interval -> TIMESTAMPTZ` | Subtract an `interval` from a `TIMESTAMPTZ` | | `interval * DOUBLE PRECISION -> interval` | Multiply an `interval` by a scalar | The `+` and `*` operators shown above come in commutative pairs (e.g., both `DATE + interval` and `interval + DATE` are accepted). ### Literal string interpretation `Interval` literals can be specified in two formats. #### Format examples **Cast from text literal**\ The following examples demonstrate different ways to cast a text literal to an interval in SQL. Each method converts a string representing a time interval into the `INTERVAL` data type using various casting syntaxes. Using interval keyword: ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} interval 'quantity unit [quantity unit...] [direction]' ``` Using double colon: ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} 'quantity unit [quantity unit...] [direction]'::interval ``` Using `CAST` function: ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} CAST('quantity unit [quantity unit...] [direction]' AS interval) ``` In these examples, `direction` can be either `ago` or left empty. Using `ago` negates all specified quantities. The `quantity` represents a signed or unsigned integer, and `unit` refers to one of the following time units, matched case-insensitively: | Unit | | :----------------------- | | microsecond\[s] / us | | millisecond\[s] / ms | | second\[s] / s | | minute\[s] / m | | hour\[s] / h | | day\[s] / d | | week\[s] / w | | month\[s] / mon\[s] | | year\[s] / y | | decade\[s] / dec\[s] | | century / centuries / c | | millennium\[s] / mil\[s] | Each `unit` can appear only once in an interval literal. The value of the interval is determined by adding the quantities of the specified units with the appropriate signs. **Unit outside of text literal**\ The following example demonstrates how to cast a numeric value to an interval by placing the unit outside of the text literal. ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} interval 'N' unit ``` In this format, `N` represents a signed or unsigned integer, and `unit` specifies the time unit, matched case-insensitively, from the following options: | Unit | | :----- | | second | | minute | | hour | | day | | week | | month | | year | ### Arithmetic between interval and TIMESTAMPTZ Interval arithmetic with `TIMESTAMPTZ` values works as follows: 1. Convert the `TIMESTAMPTZ` value from Unix time to local time according to the rules of the time zone specified by the session's `time_zone` setting. 2. Add the `millennium`, `century`, `decade`, `year`, `month`, `week` and `day` components of the interval to the local time. 3. Convert the local time back to Unix time according to the rules of the time zone specified by the session's `time_zone` setting. 4. Add the `hour`, `minute`, `second`, `millisecond`, and `microsecond` components of the interval to the Unix time. The back and forth between Unix time and local time is necessary to handle the fact that not all days consist of 24 hours due to daylight savings time transitions. For instance, `SELECT TIMESTAMPTZ '2022-10-30 Europe/Berlin' + interval '1 day'` returns `2022-10-31 00:00:00+01` but `SELECT TIMESTAMPTZ '2022-10-30 Europe/Berlin' + interval '24 hours'` returns `2022-10-30 23:00:00+01` (assuming the value of the session's `time_zone` setting is `'Europe/Berlin'`). Still, the dependence on the session's `time_zone` setting should be kept in mind when doing arithmetic between interval and `TIMESTAMPTZ`. #### Multiplying an interval by a scalar You can use the expression `date_time + INTERVAL * d` where `date_time` is a constant or column reference of type `DATE`, `TIMESTAMP`, or `TIMESTAMPTZ`, and `d` is a constant or column reference of type `DOUBLE PRECISION`. The effect is that the INTERVAL is scaled by `d`, and the resulting INTERVAL is added to `date_time`. E.g., `INTERVAL '1 day' * 3` -> `INTERVAL '3 days'`. ### Examples ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} SELECT DATE '1996-09-03' - interval '1 millennium 5 years 42 day 42 ms'; --> 0991-07-22 23:59:59.958 SELECT TIMESTAMP '1996-09-03 11:19:42' + interval '10 years 5 months 42 days 7 seconds'; --> 2007-03-17 11:19:49 SELECT TIMESTAMP '2023-10-20 11:49:52' + interval '1 year 6 months 4 weeks 7 hours' * 7.5; --> 2035-08-20 16:19:52 SELECT DATE '2023-10-20' - 42 * interval '1 months 1 day 1 hour'; --> 2020-03-07 06:00:00 -- The following example shows a daylight savings time change in the time zone 'Europe/Berlin' SET time_zone = 'Europe/Berlin'; SELECT TIMESTAMPTZ '2022-10-30 Europe/Berlin' + interval '1 day'; --> 2022-10-31 00:00:00+01 SELECT TIMESTAMPTZ '2022-10-30 Europe/Berlin' + interval '24' hour; --> 2022-10-30 23:00:00+01 SET time_zone = 'US/Pacific'; SELECT TIMESTAMPTZ '2022-10-30 Europe/Berlin' + interval '1 day'; --> 2022-10-30 15:00:00-07 SELECT TIMESTAMPTZ '2022-10-30 Europe/Berlin' + interval '24' hour; --> 2022-10-30 15:00:00-07 ``` ## Logical Logical operators return the result of a boolean operation using [three valued logic](https://en.wikipedia.org/wiki/Three-valued_logic) | Operator | Example | Explanation | | :------- | :-------- | :---------------------------- | | `AND` | `x AND y` | True if both x and y are true | | `NOT` | `NOT x` | True if x is false | | `OR` | `x OR y` | True if either x or y is true | ## Comparison | Operator | Syntax | Explanation | | :--------------------- | :------------------------- | :------------------------------------------------------------------------------------------------------------------- | | `=` | `a=b` | a is equal to b. | | `!=` | `a!=b` | a is not equal to b. | | `<>` | `a<>b` | a is not equal to b. | | `<=` | `a<=b` | a is less than or equal to b. | | `>` | `a>b` | a is greater than b. | | `>=` | `a>=b` | a is greater than or equal to b. | | `<` | `a= 100; ``` ## CASE Conditional expression similar to if-then-else statements. If the result of the condition is true, then the value of the CASE expression is the result that follows the condition. If the result is false, any subsequent WHEN clauses (conditions) are searched in the same manner. If no WHEN condition is true, then the value of the case expression is the result specified in the ELSE clause. If the ELSE clause is omitted and no condition matches, the result is NULL. ### Syntax ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} CASE WHEN THEN [ WHEN ...n ] [ ELSE ] END; ``` ### Parameters | Parameter | Description | Supported input types | | :------------ | :------------------------------------------------------------------------------------------------------------------------------------------------ | :-------------------- | | `` | A condition can be defined for each `WHEN`, and `ELSE` clause. | `BOOLEAN` | | `` | The result of any condition. Every `THEN` clause receives a single result. All results in a single `CASE` function must share the same data type. | Any | ### Return type Same data type as `` ### Example This example references a table `player_level` with the following columns and values: | player | currentlevel | | :---------- | :----------- | | kennethpark | 3 | | esimpson | 8 | | sabrina21 | 11 | | rileyjon | 15 | | burchdenise | 4 | The following example categorizes each entry by length. If the movie is longer than zero minutes and less than 50 minutes it is categorized as SHORT. When the length is 50-120 minutes, it's categorized as Medium, and when even longer, it's categorized as Long. ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} SELECT player, currentlevel, CASE WHEN length > 0 AND length <= 5 THEN 'Beginner' WHEN length > 5 AND length <= 12 THEN 'Intermediate' WHEN length > 12 THEN 'Expert' END ranking FROM player_level ORDER BY player; ``` **Returns**: | player | currentlevel | ranking | | :---------- | :----------- | :----------- | | kennethpark | 3 | Beginner | | esimpson | 8 | Intermediate | | sabrina21 | 11 | Intermediate | | rileyjon | 15 | Expert | | burchdenise | 4 | Beginner | ## String To concatenate strings, you can use the `CONCAT` function. ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} SELECT concat('This', ' is', ' a', ' parenthetical', 'concatenation.') AS concatenated_String ``` Alternatively, you can use the double pipe `||` operator. ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} SELECT 'This' || ' is' || ' a' || ' double pipe' || ' concatenation.' AS concatenated_String ``` ## Subquery operators Subqueries are queries nested within another query. They allow complex data retrieval by enabling a query to filter results based on the outcome of another query. Subquery operators are crucial in constructing these nested queries, especially within the WHERE clause, to filter data based on specific conditions. | Operator | Explanation | | :----------- | :-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `EXISTS` | The `EXISTS` operator is used to check for the existence of any record in a subquery. It returns TRUE if the subquery returns one or more records. The subquery within EXISTS is executed repeatedly, once for each row that might be selected by the outer query. If the subquery returns any row, the EXISTS condition is met, and the outer query processing continues for that row. | | `NOT EXISTS` | It is opposite of EXISTS and is used to find records in one table that have no related records in another table. If the subquery returns no rows, NOT EXISTS returns TRUE. | | `IN` | The `IN` operator checks if a specific value is present in a list of values or the results of a subquery. Commonly utilized in a `WHERE` clause, it compares a column's value against a predefined set. When the column's value matches any value in this set, `IN` yields `TRUE`. | | `NOT IN` | Retrieve all entries from the value list that don't match the required value. | ### Example–using EXISTS to find all suppliers with products equal to the price of 22 ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} SELECT supplier_name FROM suppliers WHERE EXISTS ( SELECT product_name FROM products WHERE products.supplier_id = suppliers.supplier_id AND price = 22); ``` ### Example–using the IN operator to return all customers from Mannheim or London ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} SELECT customer_name FROM customers WHERE customer_address IN ('Mannheim','London'); ``` ### Example–using a correlated subquery to retrieve all the products that cost more than the average price ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} SELECT product_id, product_name, list_price FROM products p WHERE list_price > ( SELECT AVG( list_price ) FROM products WHERE category_id = p.category_id); ``` ### Example–using a scalar boolean subquery to retrieve rows based on true/false condition ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} SELECT * FROM products WHERE ( SELECT CASE WHEN MIN(list_price) > 100 THEN true ELSE false END FROM products); ```