Marketing Cloud SQL: boolean != 'True' doesn't work as expected

Question

In Marketing Cloud, I have 2 data extensions. One holds subscriber keys and basic profile information that gets overwritten, one holds the subscriber keys and extended profile information that cannot be overwritten. One of the fields in the extended profile information is a boolean field. It does not have a default value, and is set to true (and later to false) in a journey.

I also have an automation, where an SQL query calculates a segment of newsletter subscribers. I want to exclude everyone for whom the value of that boolean field is true.

My (simplified) query was as follows:

SELECT b.SubscriberKey, b.Email
FROM Basic b
LEFT JOIN Extended e
ON b.SubscriberKey = e.SubscriberKey
WHERE b.OptIn = 'True'
AND e.Boolean != 'True'

But even though my Extended data extension only contains SubscriberKeys at this point, and all Boolean values are empty, the result of the query is 0 records.

Reversing the AND statement does seem to work:

SELECT b.SubscriberKey, b.Email, b.Name
FROM Basic b
LEFT JOIN Extended e
ON b.SubscriberKey = e.SubscriberKey
WHERE b.OptIn = 'True'
AND (e.Boolean IS NULL OR e.Boolean = 'False')

Can anyone explain why this is?

Answer 1

You say in Extended all boolean values are empty, so they are NULL. I believe making a Field != 'value' statement in SQL includes all other records where Field has a value different from 'value', but it does not include records where Field has no value.

Your second query returns results because of e.Boolean IS NULL.

I hope that helps.

By the way: Are you sure OptIn in Basic is defined as a Boolean and not as a string? I am surprised b.OptIn = 'True' would give any results if Basic is defined as a boolean. We created a product that helps users build selections through a UI without having to write SQL queries. In the queries our product builds, we always use 0 for False and 1 for True when comparing Boolean values.