Query All Subscribers for SubKey based on email address in DE

Question

I have email addresses in a Data Extension and am trying to query All Subscribers to pull in their Subscriber Key. I wrote my Query, it validates, ran the SQL and it returned no records. Not sure where I went wrong.

Select
a.SubscriberKey,
EmailAddress
from [_subscribers] a
join [NameofDEwhereEmailAddressesAreStored] de
on de.Email = a.EmailAddress

I then have a new DE to point the SQL query to put the information.

Answer 1

Moving from Comment to Answer for better visibility:

To start with, I would clean up your query a bit to ensure it is not something with a misinterpretation of ambiguity. Although the default join is inner join, I would recommend explicitly stating it to ensure understanding of your intention as well as to make it an easy reference when reviewing the code. Also, I would put an alias on EmailAddress in the Select statement (a.EmailAddress) to again remove ambiguity and reduce risk of misinterpretation.

Outside of that, I would verify if you are in a child account or the parent account. Because if you are in a child account, you should use the prefix ENT. on the _Subscribers Data View. Making it become from ENT.[_subscribers] a.

This tells the query to look at the enterprise level (parent) for the data view, which is where the 'All Subscribers' list lives.