Why DESelect Segment adds a "OR field IS NULL" to the SQL Query when creating DOES NOT CONTAIN filters?

If you have used a Contains filter in DESelect, you might have noticed that, apart from the equivalent LIKE filter in SQL, we add an extra statement, checking if the field is NULL. Let's see an example.

Let's imagine we have the following filter in DESelect Segment:

When translating this to SQL, it would be fair to assume the resulting code would be:

WHERE "CSLab_Customer"."Email" NOT LIKE '%deselect.com%'

However, when we run preview, we see the SQL generated is as follows:

WHERE (
   ( "CSLab_Customer"."Email" NOT LIKE '%deselect.com%' )
   OR "CSLab_Customer"."Email" IS NULL 
)

Understanding the default LIKE behavior in SQL

DESelect adds this extra validation to bridge the difference between DOES NOT CONTAIN (in natural language) vs LIKE in SQL.

When you do a DOES NOT CONTAIN, you'd expect that any value other than the string you introduced would be selected. However, when using NOT LIKE in SQL, it automatically excludes empty values. This is just a standard SQL behavior, not related to DESelect.

Therefore, if DESelect didn't add that statement to include NULL values when you do DOES NOT CONTAIN "X", you'd also be doing "and it's not empty".

To avoid this, we have to explicitly include back empty values (with the additional statement shown above).