User GuidesAPI ReferenceRelease Notes
Doc HomeHelp CenterLog In

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;