Create a select query (mysql)

Question

MySQL

Help make a request.
There is a mails table in which there are two fields email and status
The table has 5 rows

Email1 status1
Email2 status1
Email3 status1
Email1 status2
Email2 status2

That is, as we can see, Email1 and Email2 have two records with different statuses.
We need to create a query that would make a selection from this table and return us only emails with status 1, which are not in the table with status 2.
That is, in our case, this would be the only record - Email3

Answer 1

SELECT m1.*
FROM mails AS m1
LEFT JOIN mails AS m2 
  ON m1.email = m2.email AND m2.`status` = 'status2'
WHERE 
  m1.`status` = 'status1' 
  AND m2.email IS NULL

Answer 2

And here is a little perversion for which we all love SQL so much:

SELECT email, `status` FROM (
  SELECT email, `status`, COUNT(*) AS cnt 
  FROM mails 
  GROUP BY email
  HAVING `status` IN ('status1', 'status2')
) AS subq WHERE cnt = 1;

Answer 3

Something like this:
SELECT * FROM table WHERE email NOT IN (SELECT email FROM table WHERE email NOT LIKE "status2")

Answer 4

select email from t where email not in (select email from t where status = 'status2') and status = 'status1'
But this is clumsy, think about database architecture or logic.

Answer 5

SELECT m1.* FROM mails AS m1 
WHERE NOT EXISTS(
  SELECT * FROM mails AS m2 
  WHERE 
    m2.email = m1.email AND m2.`status` = 'status2'
)

But the first option is prettier.