User GuidesAPI ReferenceRelease NotesEnrichment APIs

# Using Logical Comparators

Logical comparators are expressions that compare values and return `true` or `false`.

There are are many ways to compare data using transformations. Logical comparators are most often used in `JOIN`, `LOOKUP`, and `FILTER` statements and in `case` expressions.

Syntax

Data Types

Example

Explanation

IS NULL

Any

state IS NULL

Returns `true` when state is a true `null` value.

IS NOT NULL

Any

state IS NOT NULL

Returns `true` when state is anything other than a true `null` value.

IS EMPTY

Any

state IS EMPTY

Returns `true` when state is a true `null` value, a blank string (a string with no content `''` or a string containing only whitespace characters like spaces and tabs `' '`), an empty array (`[]`), or an array containing only blank strings and true nulls (`[' ', null]`)

IS NOT EMPTY

Any

state IS NOT EMPTY

Returns `true` when state is anything other than a true `null` value, a blank string (a string with no content `''` or a string containing only whitespace characters like spaces and tabs `' '`), an empty array (`[]`), or an array containing only blank strings and true nulls (`[' ', null]`)

==

Any

state == 'MA'

Returns `true` when state is equal to `'MA'`

=

Any

state = 'MA'

Returns `true` when state is equal to `'MA'`

!=

Any

state != 'MA'

Returns `true` when state is not equal to `'MA'`

>

Any Numeric

cost > 10.0

Returns `true` when cost is greater than 10.0

>=

Any Numeric

cost >= 10.0

Returns `true` when cost is greater than or equal to 10.0

<

Any Numeric

cost < 10.0

Returns `true` when cost is less than 10.0

<=

Any Numeric

cost <= 10.0

Returns `true` when cost is less than or equal to 10.0

You can modify a logical comparator by using `NOT` to reverse its logic. Logical comparators can also be combined with `AND` and `OR`.

Tip: To provide additional flexibility, functions are also available for several of these logical comparators. For example, ISNULL and IS_NOT_EMPTY.

# Examples of Using Logical Comparators

Keep (that is, filter down to) rows that have a populated company_name or have a populated parent_company_name:

``````FILTER company_name IS NOT EMPTY OR parent_company_name IS NOT EMPTY;
``````

Look up the exchange rate of a currency for a given year by matching on exact values in the year and currency columns.

``````LOOKUP exchange_rate
FROM "currency_exchange.csv" AS currency_lookup
ON year == currency_lookup.year AND currency == currency_lookup.currency;
``````

Create a new attribute `price_in_words` and set it to 'expensive' when the price is greater than 100. Otherwise, set it to 'cheap':

``````SELECT *,
CASE
WHEN price > 100 THEN 'expensive'
ELSE 'cheap'
END AS price_category;
``````