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.