Select rows that contain data from any of the rows in another table

Question

I am attempting to return all the email addresses that contain any of the values from another table.

SELECT EMAIL
FROM 
  DBO.EMAIL_ADDRESSES AS A,
  DBO.EMAIL_VALUE_LOOKUP AS B
WHERE 
  POSITION(B.EMAIL_VALUE IN A.EMAIL) <> 0

Does this answer your question? [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) — Daniel Brose, May 07 '20 at 05:11

score 2 · Accepted Answer · answered May 07 '20 at 05:09

Let's roll forward about 30 years:

SELECT e.email
FROM 
  dob.email_addresses e
  INNER JOIN dbo.email_value_lookup l ON e.email LIKE '%' + l.email_value + '%'

You might want to use DISTINCT too, as if emails match multiple different values they will be repeated. This could be done implicitly by using a coordinated Exists query:

SELECT e.email
FROM 
  dob.email_addresses e
WHERE EXISTS(
  SELECT null FROM dbo.email_value_lookup l WHERE e.email LIKE '%' + l.email_value + '%')

Select rows that contain data from any of the rows in another table

1 Answers1