SFMC: Query Subscribers with EmailName NOT LIKE but also include subscribers who have never received an email

Question

I have created a Query which will query for subscribers from a certain list who have not received an email which its name contains "newsletter_"

This is working perfect

SELECT l.[EmailAddress], e.[First_name]
FROM _Job j with (nolock)
INNER JOIN _Sent s with (nolock)
ON j.JobID = s.JobID
INNER JOIN _ListSubscribers l with (nolock)
ON l.subscriberID = s.subscriberID
INNER JOIN _EnterpriseAttribute e with (nolock)
ON l.subscriberID = e._subscriberID
WHERE s.EventDate < GetDate()
AND e.Segment = 'Customer'
AND j.EmailName NOT LIKE 'newsletter_%'
AND l.ListID = 919445

the problem is, is this is not bringing back subscribers who have never received and email at all as they are not in the _sent / _job does anyone know how i am able to add subscribers to this query who have never received an email

Thank you in advance

Answer 1

Could you make a separate query using a NOT EXISTS clause and then merge the results?

SELECT l.[Email_Address], e.[First_name]
FROM _ListSubscribers
WHERE NOT EXISTS (
  SELECT 1 
  FROM _Sent 
  WHERE _Sent.[Email_Address] = _ListSubscribers.[Email_Address]
)