Links

BETWEEN

Range operator: BETWEEN

The BETWEEN operator tests if a value is within a specified range.
It uses the syntax value BETWEEN min AND max:
CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data MAP_COLUMNS_BY_NAME
SELECT
3 BETWEEN 2 AND 6 as between_example -- evaluates to true
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE time_filter()
LIMIT 1;
The statement shown above is equivalent to the following statement:
CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data MAP_COLUMNS_BY_NAME
SELECT
3 >= 2 AND 3 <= 6 as between_example -- evaluates to true
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE time_filter()
LIMIT 1;
To test if a value does not fall within the specified range use NOT BETWEEN:
CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data MAP_COLUMNS_BY_NAME
SELECT
3 NOT BETWEEN 2 AND 6 as between_example -- evaluates to false
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE time_filter()
LIMIT 1;
The statement shown above is equivalent to the following statement:
CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data MAP_COLUMNS_BY_NAME
SELECT
3 < 2 OR 3 > 6 as between_example -- evaluates to false
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE time_filter()
LIMIT 1;
The presence of null in a BETWEEN or NOT BETWEEN statement will result in the statement evaluating to null:
CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data MAP_COLUMNS_BY_NAME
SELECT
null::bigint BETWEEN 2 AND 4 as between_example1, -- evaluates to null
2 BETWEEN null::bigint AND 6 as between_example2 -- evaluates to null
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE time_filter()
LIMIT 1;
The BETWEEN and NOT BETWEEN operators can also be used to evaluate string arguments:
CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data MAP_COLUMNS_BY_NAME
SELECT
'Paul' BETWEEN 'John' AND 'Ringo' as between_example, -- evaluates to true
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE time_filter()
LIMIT 1;
Note that the value, min, and max parameters for BETWEEN and NOT BETWEEN must be of the same type. For example, the query produces an error if you ask if 'John' BETWEEN 2.3 AND 35.2.