mysql opposite of inner join

Question

I want to contact my users via email using my database. I want to make sure that I don't accidentally contact the same user twice. For that, I have a table that tracks who got contacted and when.

When I do my MYSQL query I want to select emails from the the email table and make sure none of those entries exists in the contacted table.

To phrase it in a sentence: select email from Email_Table if they are not in Contacted_Table

Perhaps there is a completely different approach. I am open to all suggestions :)Thank you :)

Answer 1

Try this

SELECT email FROM email_table e 
LEFT JOIN contacted_table c ON e.email = c.email
WHERE c.email IS NULL

Answer 2

select email 
from Email_Table t1 
where not exists (select email 
                  from Contacted_table t2 
                  where t1.email = t2.email)

OR

select email 
from Email_Table t1 
where email not in (select email 
                    from Contacted_table)

Answer 3

If you try to do a left join like this:

SELECT users.email, contacted.email
FROM users LEFT JOIN contacted ON users.email = contacted.email

You will get a similar result:

users.email | contacted.email
-----------------------------
[e-mail]   | [e-mail]
[e-mail]   | [e-mail]
[e-mail]   | [e-mail]
[e-mail]   | NULL
[e-mail]   | NULL

Your goal is to get those record which do not have a match in the contacted table, so your query would be:

SELECT users.email
FROM users LEFT JOIN contacted ON users.email = contacted.email
WHERE contacted.email IS NULL