Contents

WHERE function

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE TIMESTAMP, VARCHAR, BOOLEAN

Filter returned rows from a table. The following operators are supported in WHERE statements: comparison , logical, and IN operators and IS operators.

Examples

Comparison operators

These include operators like less than or equal to. Refer to comparison operators for a full list of supported syntax.

SELECT *
FROM <table_name>
WHERE <col_1> = <number>

SELECT *
FROM <table_name>
WHERE <col_1> <= <number>

Selecting rows can also be done by comparing values in columns with comparable data types: numeric with numeric, timestamp with timestamp , string with string (only = is supported).

SELECT *
FROM <table_name>
WHERE <col_1> >= <col_2>

Logical operators

When using the WHERE statement rows are selected where the predicate for the row evaluates to TRUE: in first the example above only rows where col_1 are equal to some number will be selected.

These statements can be further chained to create more complex logical statements.

SELECT *
FROM <table_name>
WHERE <col_1> => <number_1>
    AND <col_2> <= <number_2>

SELECT *
FROM <table_name>
WHERE <col_1> BETWEEN <number_1> AND <number_2>
    OR <col_2> <= <number_3>

Refer to logical operators for a full list of supported syntax.

IN operators

Filtering data to some subset of allowed values can be achieved with IN operators.

SELECT *
FROM <table_name>
WHERE <col_1_string> IN (<string_1>, <string_2>)

The above example is an equivalent to:

SELECT *
FROM <table_name>
WHERE <col_1_string> = <string_1>
    OR <col_1_string> = <string_2>

To select all the rows but those that are equal to string_1 or string_2, the IN statement can be negated:

SELECT *
FROM <table_name>
WHERE <col_1_string> NOT IN (<string_1>, <string_2>)

If the list of strings is long a subquery can be included within the parentheses:

SELECT *
FROM <table_1>
WHERE <col_1_string> IN (
    SELECT <col_2_string>
    FROM <table_2>
    WHERE <col_1> >= <number>
)

Refer to IN operators for a full list of supported syntax.

IS operators

Filtering data using BOOLEAN columns can be achieved by simply passing the column itself (or its negation) as a predicate:

SELECT *
FROM <table_name>
WHERE <col_1>

SELECT *
FROM <table_name>
WHERE NOT <col_1>

The first example above code is equivalent to

SELECT *
FROM <table_name>
WHERE <col_1> IS TRUE

or

SELECT *
FROM <table_name>
WHERE <col_1> IS NOT FALSE

Refer to IS operators for a full list of supported syntax.