how to use query in marketing cloud that uses data extension for where clause?

Question

Right now I have this query which works great:

SELECT SubscriberID, EmailAddress
FROM ent._Subscribers WITH (NOLOCK)
WHERE SubscriberID = '1234567890'

But, since I want to find out EmailAddress of 1000s of SubIDs, I cannot do this manually by running query for each SubID.

I have a list of Subscriber IDs saved in a data extension in marketing cloud.

Is there any way I can run a query and it looks for the SubIDs from my data extension and saved the linked email address in a different DE along with SubID?

Answer 1

You can add a subquery to your where-clause. This would look like this:

SELECT allsub.SubscriberID, allsub.EmailAddress
FROM ent._Subscribers as allsub WITH (NOLOCK)
WHERE allsub.SubscriberID IN (
    SELECT ids.SubscriberID FROM YourOtherDataExtension as ids
)