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?