Search does not work correctly when using JOIN

Question

I have the SQL below and the parameter where the part JOIN contratos co ON c.email != co.email is not working. My goal is that the c.email is not in the contracts table. No SQL error is shown.

If I do so JOIN contratos co ON c.email = co.email works perfectly... but I want the opposite result of this.

SELECT os.osID, c.nome, c.email, os.idioma, os.dataAtualizacao
FROM os JOIN cadastroCliente c ON os.idcliente = c.cadastroClienteID AND c.email != '' JOIN contratos co ON c.email != co.email
WHERE os.status = '1' 
AND os.respondido = '1'
AND os.emailAutRecuperar1 = '0'
AND os.dataAtualizacao < CURRENT_DATE()-3 GROUP BY osID

I have tried otherwise but also unsuccessfully.

SELECT os.osID, c.nome, c.email, os.idioma, os.dataAtualizacao
FROM os, contratos co JOIN cadastroCliente c ON os.idcliente = c.cadastroClienteID AND c.email != ''
WHERE os.status = '1' 
AND os.respondido = '1'
AND os.emailAutRecuperar1 = '0'
AND os.dataAtualizacao < CURRENT_DATE()-3 AND c.email != co.email GROUP BY osID

Answer 1

Ever tried to use NOT IN?

This command would filter c.Email not in (select co.Email from Contratos co)

WHERE c.Email NOT IN
(
SELECT co.Email from Contratos co
)