Link Search Menu Expand Document

This documentation is related to an older version of Firebolt. For the most current documentation, see Firebolt documentation.

Operators

Arithmetic

Operator Operator description Example Result
+ addition SELECT 2 + 3; 5
- subtraction SELECT 2 - 3; -1
* multiplication SELECT 2 * 3; 6
/ division (integer division truncates the result) SELECT 4 / 2; 2
% modulo (remainder) SELECT 5 % 4; 1
^ exponentiation SELECT 2.0 ^ 3.0; 8

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.

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<b a is less than b.
BETWEEN a BETWEEN b AND c equivalent to b <= a <= c
IS NOT DISTINCT FROM a IS NOT DISTINCT FROM b equivalent to a=b where NULL is considered equal to NULL.
IS DISTINCT FROM a IS DISTINCT FROM b equivalent to a!=b where NULL is considered equal to NULL.

Example of using comparison operator in WHERE clause

SELECT
  *
FROM
  Table
WHERE
  Price >= 100;

String

To concatenate strings, you can use the CONCAT function.

SELECT concat('This', ' is', ' a', ' parenthetical', 'concantenation.') AS concatenated_String

Alternatively, you can use the double pipe || operator.

SELECT 'This' || ' is' || ' a' || ' double pipe' || ' concantenation.' AS concatenated_String

Boolean

Boolean operators return the result of a Boolean operation between one or more expressions.

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

INTERVAL for date and time

Use the INTERVAL operator to add to or subtract from a period of time in DATE, TIME, or TIMESTAMP data types.

Syntax 

{ +|- } INTERVAL '<quantity> [ <date_unit> ] [ ...]'
Component Description
<quantity> An integer. Multiple <quantities> and <date_units> can be used in the same INTERVAL command if they are separated by spaces.
<date_unit> A date measurement including any of the following: millennium, century, decade, year, month, week, day, hour, minute, second, millisecond, microsecond or their plural forms. If unspecified, <date_unit> defaults to second.

Example 

<date_column> + INTERVAL '1 year 2 months 3 days'
<date_column> - INTERVAL '2 weeks'
<date_column> - INTERVAL '1 year 3 hours 20 minutes'

:: operator for CAST

Use can use the :: operator instead of the CAST function to convert one data type to another.

Syntax 

 -- CAST function
 CAST(<value> AS <type>)
 -- :: operator
 <value>::<type>
Component Description
<value> 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.
<type> The target data type (case-insensitive).

Example 

SELECT '2021-12-31'::DATE;
SELECT 8.5::REAL;
SELECT col_a::BIGINT;

Subquery operators

Subqueries are queries contained within other queries. They are typically used as part of a WHERE clause to return entries based on the existence or absence of a condition.

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.
NOT EXISTS The NOT EXISTS operator returns TRUE if the underlying subquery returns no record.
IN The IN operator is used to check whether a value matches any value in a list.
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 

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 

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 avgerage price 

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 

SELECT
  *
FROM
  products
WHERE (
  SELECT CASE WHEN
    MIN(list_price) > 100
  THEN
    true
  ELSE
    false
  END
  FROM
    products);