Extracting Email Addresses by Domains from Multiple Data Extensions?

Question

I am trying to extract all Verizon owned email domains from existing data extensions in SFMC and placing these subscribers into a new DE that will contain only the subscribers who use a Verizon domain.

Select *
FROM "Data Ext 1" AND
"Data Ext 2"
WHERE
EmailAddress LIKE '%@aol.com' AND
EmailAddress LIKE '%@yahoo.com' AND
EmailAddress LIKE '%@netscape.com' AND
EmailAddress LIKE '%@verizon.com'

However, this is not working. If I remove the second DE, it still won't extract the domains if I use a LIKE statement, however, if I use a NOT LIKE statement my new DE gets populated with all subscribers who DON'T use a Verizon email domain. Why is it working one way but not the other?

Answer 1

You can't select rows from all data extensions in a single query without doing a union of them by name. To select rows from additional data extensions, simply append another UNION and another query. A UNION selects distinct, while UNION ALL will return duplicates.

Select emailaddress
FROM [Data Ext 1] 
WHERE
EmailAddress LIKE '%@aol.com' 
or EmailAddress LIKE '%@yahoo.com'
or EmailAddress LIKE '%@netscape.com'
or EmailAddress LIKE '%@verizon.com'

union 

Select emailaddress
FROM [Data Ext 2] 
WHERE
EmailAddress LIKE '%@aol.com' 
or EmailAddress LIKE '%@yahoo.com'
or EmailAddress LIKE '%@netscape.com'
or EmailAddress LIKE '%@verizon.com'