# Null safe "is not" filter condition

**URL:** <https://community.holistics.io/t/null-safe-is-not-filter-condition/1433>\
**Category:** Feature Suggestions\
**Tags:** filters, sql\
**Created:** [February 22, 2023, 5:49am UTC](https://community.holistics.io/t/null-safe-is-not-filter-condition/1433 "2023-02-22T05:49:36Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![jingyu](https://community.holistics.io/user_avatar/community.holistics.io/jingyu/32/671_2.png) [@jingyu](https://community.holistics.io/u/jingyu)\
**Post date:** [February 22, 2023, 5:49am UTC](https://community.holistics.io/t/null-safe-is-not-filter-condition/1433/1 "2023-02-22T05:49:36Z")

</div>

**Problem**  
Most operators in Snowflake are not null safe, which causes confusion among non-technical data users when using the **is not** operator in “Conditions”.

Some users who are not familiar with the data may even be unaware that they are not retrieving their intended output.

**Example**  
A `bank_name` column contains the following values:

- _NULL_

- `bank_a`

- `bank_b`

- `bank_c`

In most cases, users want to retrieve all rows that are **not equal to** `bank_a` **or** `bank_b` (including NULL). In order to achieve this, the WHERE condition has to be:

`where coalesce(bank_name, 'NA') not in ('bank_a','bank_b')`

instead of

`where bank_name not in ('bank_a','bank_b')`

This is because Snowflake will treat NULLs as unknown values in the 2nd case, and exclude them from the comparison (and therefore the query output).

**Current Workaround**  
We define a custom dimension from `bank_name` which assigns a `'NA'` to NULL values, and hide the `bank_name` column.

However, this is not ideal because:

1. Reports using the `bank_name` dimension previously have to be updated to use new custom dimension

2. The same behaviour will be observed for other columns, and it is not scalable to recreate custom dimensions for all.

3. We want to keep NULL values as is for some columns

---

<div class="post-metadata">

**Author:** ![Khai\_To](https://community.holistics.io/user_avatar/community.holistics.io/khai_to/32/128_2.png) [@Khai\_To](https://community.holistics.io/u/Khai_To)\
**Post date:** [February 27, 2023, 9:28am UTC](https://community.holistics.io/t/null-safe-is-not-filter-condition/1433/2 "2023-02-27T09:28:38Z")

</div>

Hi @jingyu,  
I do agree that the Null behavior in Holistics could be handled much better, thank you for sharing a very detailed use case and example.  
I will add this to our backlog for future improvement 😁