CODEWITHSUNDEEP
mysqlsql
orangebreeze
orangebreeze

Reputation: 147

MySQL SELECT rows that partly match a string from other TABLE

I have two tables: one with emails and the other table with domains. 

Table1                  Table2
id email                id  domain
-- ----                 --  ----
1  [email protected]     1   domain1.com
2  [email protected]     2   domain4.com
3  [email protected]
4  [email protected]

Now I want to select all email from table 1 where the domain matches the domain field of table 2. The result should be:

id email
-- ----
1  [email protected]
4  [email protected]

I guess it would work with a combination of REGEXP and INNER JOIN? But I don't know how to combine them.

Upvotes: 2

Views: 5947

Answers (3)

bobflux
bobflux

Reputation: 11581

SOlution 1 :

Use Table1.email LIKE CONCAT('%@',Table2.domain).

BIG FAT RED WARNING : This condition is not indexable. If you want speed, split the email into address and domain in Table1, and create an index.

EDIT :

In fact this JOIN condition will need count(email)xcount(domain) comparisons, so it's probably the slowest way.

Solution 2:

Rob has a very good point : a faster way is to extract the domain from the email (using substring) and match this against an index on the domains table

SOlution 3 :

The best way is to index the email's domain. In Postgres you would create a function index ON extract_domain(email) (supposing you create a trivial extract_domain() function), but you cannot do this in MySQL, so an extra column with the domain only is the way to go for speed.

If you want to know all emails in ONE domain,

Solution 1 : seq scan table emails + fast LIKE
Solution 2 : seq scan table emails + slightly slower domain extraction
Solution 3 : index scan table emails

If you want to JOIN on table domains for all domains / all emails :

Solution 1 : count(email)xcount(domain) comparisons, very slow
Solution 2 : seq scan table emails + index scan domains
Solution 3 : nested loop index join

For a full JOIN it would be even faster to use a merge join or hash join, but those are not provided by mysql.

Upvotes: 3

PachinSV
PachinSV

Reputation: 3780

I created to tables and made a test, and this query worked for me:

SELECT t1.*, t2.domain FROM t1
INNER JOIN t2 ON t1.email LIKE CONCAT('%@', t2.domain);

Upvotes: 4

Raj More
Raj More

Reputation: 48016

Try this out

SELECT t1.Id, 
       t1.Email
  FROM Table1 t1
  JOIN Table2 t2 ON t1.email LIKE CONCAT('%@', t2.Domain);

Upvotes: 2

Related Questions