MySQL: select emails from one table only if not in another table?

Question

I am going to build a table called donotemail that will contain the email addresses of people who ask to be removed from our email list. I have another table called users with an email column. How can I select all the emails from users but only if the email address is not in the donotemail table?

Thanks!

Answer 1

 select u.email from users u where u.email not in (select email from donotemail)

OR

select u.email from users u inner join donotemail d on u.email != d.email

EDIT: The join doesn't work

Answer 2

select email from users where email not in (select email from donotemail)

Answer 3

SELECT email FORM users u WHERE NOT EXISTS(
    SELECT 1 FROM no_mail_users nmu
    WHERE nmu.id = u.id
)

Answer 4

Why not just add another column to your email table, labeled Active, when set to 1 it is currently emailing, and when set to 0 it no longer emails, then you can just select and filter based on that switch?

Cheers

Answer 5

Try

SELECT Email.address
FROM Email LEFT OUTER JOIN DoNotMail on Email.address = DoNotMail.address
WHERE DoNotMail.address is null

It avoids needing a subquery.