SQL NOT LIKE PARAMETER

Question

I have the below queries in my automation.

Query 1:

SELECT
s.SubscriberKey,
FORMAT(DATEADD(hh,6,s.EventDate),'MM/dd/yyyy hh:mm:ss tt') AS EventDate,
s.JobID,
s.batchID AS BatchID,
s.TriggererSendDefinitionObjectID,
j.EmailID,
FORMAT(DATEADD(hh,6,j.ModifiedDate),'MM/dd/yyyy hh:mm:ss tt') AS JobModifiedDate
FROM [_Sent] s
JOIN [_Job] j
ON s.JobID = j.JobID
WHERE s.EventDate > FORMAT(DATEADD(dd,-1,getdate()),'yyyy-MM-dd 00:00:00') AND s.EventDate < FORMAT(DATEADD(dd,-0,getdate()),'yyyy-MM-dd 00:00:00')
AND j.SuppressTracking = 0
AND j.Category <> 'Test Send Emails'
AND
    (LOWER(j.EmailName) NOT LIKE '%proof%' AND LOWER(j.EmailName) NOT LIKE '%test%')

Result DE: DE1 Fetches records

Query 2: This query does not fetches record. However, when the where condition is removed, it gives me records. This is happening because in my BU the sends are not happening through journey. So, is the understanding correct that 'not like' parameter is not working where journeyname does not have a value.

SELECT
s.SubscriberKey,
s.EventDate,
s.JobID,
s.BatchID,
s.JobModifiedDate,
s.TriggererSendDefinitionObjectID,
s.EmailID,
su.EmailAddress AS EMAIL,
jy.JourneyID,
'SENT' AS EventType
FROM [DE1] s
JOIN ENT._Subscribers su
ON s.SubscriberKey = su.SubscriberKey
LEFT JOIN [_JourneyActivity] ja
ON s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
LEFT JOIN [_Journey] jy
ON ja.VersionID = jy.VersionID***
where (lower(jy.journeyname) NOT LIKE '%test%')

The set of queries works fine, when in the BUs that are using Journey Builder for email sends

Answer 1

The issue you are running into is SQL Three-Valued Logic. This is saying, in a nut shell, that you have 3 choices - True, False, Null.

This comes into play in instances like above. For you see, a null value will never return true or false for any condition that does not consider NULL (e.g. column IS NULL, column IS NOT NULL, etc.) or does not use a function to handle nullable values (ISNULL(column, 'false'), COALESCE(column,columnb,'false'), etc.).

This means that if you have a null value in your column and you say 'return all records where column does not equal 'fish' - your record with null will not return true or false, but instead it will return null. As only true values are pushed beyond the condition, this means that although null is not 'fish', it will not be passed through as it does not return true.

You would need to adjust it to the below to account for the null values:

SELECT
s.SubscriberKey,
s.EventDate,
s.JobID,
s.BatchID,
s.JobModifiedDate,
s.TriggererSendDefinitionObjectID,
s.EmailID,
su.EmailAddress AS EMAIL,
jy.JourneyID,
'SENT' AS EventType
FROM [DE1] s
JOIN ENT._Subscribers su
ON s.SubscriberKey = su.SubscriberKey
LEFT JOIN [_JourneyActivity] ja
ON s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
LEFT JOIN [_Journey] jy
ON ja.VersionID = jy.VersionID***
where (lower(jy.journeyname) NOT LIKE '%test%')
OR jy.journeyname IS NULL