SQL Question - how do I select all emails that belong to a list of domains from two tables?

Question

Hi guys - I have two tables here: One holds a list of subscriber emails:

(ID, email)

and the other holds a list of domains:

(ID, domain)

I want to figure out a sql statement that allows me to select * the email addresses which belong to the domains listed in the domain table assuming that all emails are of the form:

name@domain

Can this be done in sql or do I need to write a script for this?

Answer 1

You should be able to do the following:

SELECT 
    sub_emails.email
FROM 
    sub_emails
INNER JOIN
    list_domains ON 
        (sub_emails.email LIKE concat('%@', list_domains.domain_name));

Be aware that this will require a full table scan of sub_emails, since MySQL does not support function based indexes yet. Therefore this would become very slow if sub_emails contains many records.

Answer 2

Start here:

select email from EmailTable 
where substring(email, charindex('@', email) + 1, LEN(@test))
in (select domain from DomainTable)

This will fail if any of your emails are missing the @, but it will get you started.

Answer 3

SELECT email FROM emailTable WHERE 
    SUBSTRING(email, LOCATE('@',email)) IN (SELECT domain FROM domainTable)

Answer 4

SET @myregexp := CONCAT( 
 '@(',
 (SELECT GROUP_CONCAT( REPLACE(domain,'.','\.') SEPARATOR '|' ) FROM `domains` ) , 
 ')$'
);

SELECT * 
FROM `emails` 
WHERE email
REGEXP @myregexp