User GuidesAPI ReferenceRelease Notes
User Guides

# 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.

SyntaxData TypesExampleExplanation
IS NULLAnystate IS NULLReturns `true` when state is a true `null` value.
IS NOT NULLAnystate IS NOT NULLReturns `true` when state is anything other than a true `null` value.
IS EMPTYAnystate IS EMPTYReturns `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 EMPTYAnystate IS NOT EMPTYReturns `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]`)
==Anystate == 'MA'Returns `true` when state is equal to `'MA'`. Note that when one side of the comparator is `null` the final result will be `null`, for example, `'a' == null` returns `null`.
=Anystate = 'MA'Returns `true` when state is equal to `'MA'`. Note that when one side of the comparator is `null` the final result will be `null`, for example, `'a' == null` returns `null`.
!=Anystate != 'MA'Returns `true` when state is not equal to `'MA'`.Note that when one side of the comparator is `null` the final result will be `null`, for example, `'a' != null` returns `null`.
>Any Numericcost > 10.0Returns `true` when cost is greater than 10.0. Note that when one side of the comparator is `null` the final result will be `null`, for example, `5 > null` returns `null`.
>=Any Numericcost >= 10.0Returns `true` when cost is greater than or equal to 10.0. Note that when one side of the comparator is `null` the final result will be `null`, for example, ` 5 >= null` returns `null`.
<Any Numericcost < 10.0Returns `true` when cost is less than 10.0. Note that when one side of the comparator is `null` the final result will be `null`, for example, `5 \< null` returns `null`.
<=Any Numericcost <= 10.0Returns `true` when cost is less than or equal to 10.0. Note that when one side of the comparator is `null` the final result will be `null`, for example, `5 \<= null` returns `null`.

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, IS NULL 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;
``````