Comparison and Conditional Functions

This set of functions commonly returns a Boolean true or false value, a specific value from the list of arguments, or even NULL.

Function Description
BETWEEN ... AND ... Returns true if the argument is in a given range, else returns false
coalesce() Returns the first non-NULL argument
EXISTS Returns true if a subquery contains any rows, else returns false
filename_matches() Matches filenames to be included in a query
greatest() Returns the largest of the given arguments
IN Returns true if the argument is in a given list of values, else returns false
IS DISTINCT FROM Returns true is the arguments are different, else returns false
is_inf() Returns true if the given number is infinite, else returns false
is_nan() Returns true if the given number is not a number, else returns false
isfalse() Returns false if the given expression evaluates to false, else returns true
isnotfalse() Returns true if the given expression evaluates to true, else returns false
isnottrue() Returns false if the given expression evaluates to false, else returns true
isnull() Returns true if the given argument is NULL, else returns false
istrue() Returns true if the given expression evaluates to true, else returns false
NOT IN Returns true if the argument is not in a given list of values, else returns false
nonnullvalue() Returns true if the given argument is not NULL, else returns false
nullifzero() Returns NULL if the given argument is 0, else returns the argument value
nullvalue() Returns true if the given argument is NULL, else returns false
typeof() Returns the type of a given argument
zeroifnull() Returns 0 if the given argument is NULL, else returns the argument value

The following explains the functions and statements in more detail.

  • BETWEEN ... AND ...

    Returns true if the argument is in a given range, else returns false. Syntax is <expression> BETWEEN <min_value> AND <max_value>.

    Example: Basic BETWEEN operator

    > SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1;
    true, false
    

    Example: Using the BETWEEN operator as a predicate

    > SELECT * FROM okera_sample.users
    WHERE ccn BETWEEN "0000-0000-0000-0000" AND "4444-4444-4444-4444";
    
    [
        {
        "gender": "F",
        "dob": "8-Apr-84",
        "uid": "0001BDD9-EABF-4D0D-81BD-D9EABFCD0D7D",
        "ccn": "3771-2680-8616-9487"
        },
        {
        "gender": "U",
        "dob": "20-Jun-02",
        "uid": "0010C6F2-8C04-450E-90C6-F28C04B50E97",
        "ccn": "3488-9280-3164-7164"
        },
        {
        "gender": "M",
        "dob": "10-Jul-91",
        "uid": "001704E0-6CD8-429A-8E0A-89024019CA6A",
        "ccn": "3771-7913-7257-5901"
        },
        {
        "gender": "M",
        "dob": "16-Feb-82",
        "uid": "001BFE35-555B-48E1-9ED3-A4BE7677C36C",
        "ccn": "3488-1287-7916-4934"
        },
        ...
    ]
    
  • <primitive_type> coalesce(<primitive_type>, ...)

    Returns the first non-NULL argument from a list of given numerical or string values (see primitive types, excluding BINARY). The types of the arguments has to match, for example, only strings or only numbers (which are inferred as shown in the example).

    Example: Using coalesce() to get first non-null value

    > SELECT coalesce(NULL,"bar","foo");
    bar
    > SELECT coalesce(NULL,NULL,NULL,1,2,3.4);
    1.0
    > SELECT coalesce(NULL,NULL,NULL);
    null
    
  • EXISTS

    Returns true if a subquery contains any rows, else returns false. Typically used in queries that filter on the presence of related data in other datasets. For example, these queries commonly follow this pattern:

    SELECT column_name(s)
    FROM table_name_1 AS t1
    WHERE EXISTS (
        SELECT column_name FROM table_name_2 AS t2
        WHERE t2.country = t1.country
    );
    
  • BOOLEAN filename_matches(STRING)

    Matches filenames to be included in a query. Since in a data lake one option to store data is files in a file system, the filename_matches() can be used to filter data based on the names of the underlying data files.

    Example: Data files named by region

    $ aws s3 ls s3://examplebucket/salesdata/ 2018-11-26 08:22:39 435434 asia.parq 2018-11-26 08:22:39 3534534 europe.parq 2018-11-26 08:22:39 3534534 latin.csv 2018-11-26 08:22:39 6572467 useast.parq 2018-11-26 08:22:39 5654642 uswest.parq ...

    Example: Query data only from US and Europe data files

    > SELECT * FROM salesdb.salesdata
    WHERE filename_matches("us*|europe*");
    

    Example: Query data only from Parquet files

    > SELECT * FROM salesdb.salesdata
    WHERE filename_matches("*.parq");
    
  • <primitive_type> greatest(<primitive_type>, ...)

    Returns the largest of the given list of arguments (see primitive types, excluding BINARY). The types of the listed arguments must align, for example all strings or all numbers. The resulting type is derived from the argument types (as shown in the last example).

    Example: Using greatest() to determine the largest argument

    > SELECT greatest(2,0);
    2
    > SELECT greatest(34.0,3.0,5.0,767.0);
    767.0
    > SELECT greatest("B","A","C");
    C
    > SELECT greatest(32.323, 1, 10000);
    10000.000
    
  • <primitive_type> IN (<primitive_type>, ...) and <primitive_type> NOT IN (<primitive_type>, ...)

    The IN operator returns true if the argument is in a given list of values, else returns false. The NOT IN is the doing the exact opposite.

    Example: Using the IN operator

    > SELECT 1 IN (1,2,3)
    true
    > SELECT 3+1 IN (1,2,3)
    false
    > SELECT 3+1 IN (1,2,2+2)
    true
    
    > SELECT 1 NOT IN (1,2,3)
    false
    
  • <primitive_type> IS DISTINCT FROM <primitive_type>

    Returns true is the arguments are different, else returns false.

    Example: Using the IS DISTINCT FROM construct

    > SELECT 10 IS DISTINCT FROM 11;
    true
    
    > SELECT 10 IS DISTINCT FROM 10;
    false
    
    > SELECT 10 * 10 IS DISTINCT FROM 1 * 100;
    false
    
  • BOOLEAN is_inf(DOUBLE)

    Returns true if the given number is infinite, else returns false.

  • BOOLEAN is_nan(DOUBLE)

    Returns true if the given number is not a number, else returns false.

  • BOOLEAN isfalse(BOOLEAN) and BOOLEAN istrue(BOOLEAN)

    The isfalse() function returns false if the given expression evaluates to false, else returns true. The istrue() function does the exact opposite, it returns true when the expressions given evaluates to true, otherwise it returns false.

    Example: Handling of NULL values

    SELECT istrue(null), isfalse(null);
    false, false
    

    Example: Using the isfalse() function to evaluate expressions

    > SELECT isfalse(1 > 0), isfalse(0 > 1)
    false, true
    

    Example: Using the istrue() function to evaluate expressions

    > SELECT istrue(1 > 0);
    true
    > SELECT istrue(1 < 0);
    false
    
  • BOOLEAN isnotfalse(BOOLEAN) and BOOLEAN isnottrue(BOOLEAN)

    The isnotfalse() function returns true if the given expression evaluates to true, else returns false. The isnottrue() function does the same for expressions evaluating to false, which includes, for example, the NULL value.

    Example: Handling of NULL values

    SELECT isnottrue(null), isnotfalse(null);
    true, true
    

    Example: Using the isnottrue() function to evaluate expressions

    > SELECT isnottrue(1 > 0);
    false
    > SELECT isnottrue(1 < 0);
    true
    
  • BOOLEAN nonnullvalue(<primitive_type>)

    Returns true if the given argument is not NULL, else returns false.

  • <numeric_type> nullifzero(<numeric_type>) and <numeric_type> zeroifnull(<numeric_type>)

    The nullifzero() function returns NULL if the given argument is 0, else returns the argument value. The zeroifnull() function returns 0 if the given argument is NULL, else returns the argument value.

    Example: Using zeroifnull() to convert NULLs to numbers

    > SELECT zeroifnull(1), zeroifnull(null);
    1, 0
    
  • BOOLEAN nullvalue(<primitive_type>)

    Returns true if the given argument is NULL, else returns false.

  • STRING typeof(<primitive_type>)

    Returns the type of a given argument.

    Example: Using typeof() to determine the type of given arguments

    > SELECT typeof(1), typeof(null), typeof(1.0), typeof("foo");
    TINYINT, BOOLEAN, DECIMAL(2,1), STRING