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
```