Unable to Retrieve Unmatched Sends from Source DE when Joining with the _Sent Data View

Question

I'm having a difficult time joining data views to get the desired result of showing from a custom query that when using a LEFT JOIN I would expect to show all records for all 80466 seen in the Source Data Extension when I join it to _Sent data view and join the _Sent Data view to the _Job data view. I get back about 73,886...equal to the number of sends (duplicates included). Please have a look and let me know if you can help identify what I might be missing to cause this. Obviously you can't see the actual data (which is confidential) and I could share a test file but couldn't replicate a test scenario on my real _Sent data view. However, I can share that (a) These emails have a subscriberKey equal to their email address and (b) the data on the source DE has a primary key of 'num' field to control the order in which records are sent to through the month during the daily send schedule and allow for multiple occurrences of an email to be sent as many do repeat on different days.

Below is the query.

Select Top 80466 a.[Email Address] as 'Email Address', a.[Branch] as 'Branch', 
a.[Link 1] as 'Link 1', a.[myBrandName] as 'myBrandName', a.[num] as 'num', s.EventDate as 'Sent Date'
FROM [February 2022 Source] a
LEFT JOIN _Sent s with (nolock) on a.[Email Address] = s.SubscriberKey
LEFT JOIN _job j with (nolock) on s.jobid = j.jobid
WHERE j.EmailName IN ('Daily February Email')
AND (s.[EventDate] >= '2022-02-01 00:00'
AND s.[EventDate] < '2022-03-01 00:00'
OR s.[Eventdate] is null)
GROUP BY a.[Email Address], a.[Branch], a.[Link 1], a.[myBrandName], a.[num], s.EventDate
ORDER BY a.[num]

When I use the LEFT JOIN above I am expecting the same behavior to be observed as I would see wanting to display a All employees from a a common hypothetical employee table (below) left joined to a project table whether the employees have a project or not.

SELECT first_name, last_name, project_name 
FROM employee e
LEFT JOIN project p
ON e.project_id = p.id;

In my custom query I am joining the Email Address column from a DE with SubscriberKey field on the _Sent data view. I usually see the data pull as expected using the Query Activity but I am wondering if the _Sent data is somehow restricting unsent addresses from being pulled in. (It sounds unlikely odd but I have heard many odd things like this with SFMC). I've tried full outer join and right join combos and still haven't gotten it to pull in the full 80466 which I am wanting to stay ordered numerically the same as they exist in the source DE. From experiences I've previously had with LEFT JOINs for both matched and unmatched record examples illustrated I still believe it should work but maybe I'm missing something.

Answer 1

** updated**

Your where clause is removing any records that don't have a send date within your time range but have a sent date. To accomplish what you want you will need to write your query different:

Select Top 80466 a.[Email Address] as 'Email Address', a.[Branch] as 'Branch', 
a.[Link 1] as 'Link 1', a.[myBrandName] as 'myBrandName', a.[num] as 'num', x.EventDate as 'Sent Date'
FROM [February 2022 Source] a
LEFT JOIN 
    (Select eventdate, subscriberkey 
     from _Sent s with (nolock)
     LEFT JOIN _job j with (nolock) on s.jobid = j.jobid
     WHERE j.EmailName IN ('Daily February Email')
     AND s.[EventDate] >= '2022-02-01 00:00'
     AND s.[EventDate] < '2022-03-01 00:00' ) x
on a.[Email Address] = x.SubscriberKey
GROUP BY a.[Email Address], a.[Branch], a.[Link 1], a.[myBrandName], a.[num], x.EventDate
ORDER BY a.[num]