minus在mysql里怎么用,MySQL中的MINUS运算符？

I have some tables where I am getting the emails. And I don't want to get the emails in table tbl_unsubscribe. I wrote the query like :

SELECT cand_email FROM tbl_cand_data

UNION

SELECT emp_email FROM tbl_emp_data

UNION

SELECT email FROM tbl_uptade_list

UNION

SELECT feed_email FROM tbl_feedback

UNION

SELECT admin_email FROM tbl_admin_emails

But I am getting a syntax error. Is the MINUS operator not valid for MySQL ?

解决方案

A NOT IN() subquery can be used here, since MySQL doesn't support MINUS.

SELECT

cand_email

FROM tbl_cand_data

WHERE can_email NOT IN (SELECT un_email FROM tbl_unsubscribe)

It can also be done with a LEFT JOIN, looking for NULLs in the un_email column:

SELECT cand_email

FROM

tbl_cand_data

LEFT JOIN tbl_unsubscribe ON tbl_cand_data.can_email = tbl_unsubscribe.un_email

WHERE tbl_unsubscribe.un_email IS NULL

To exclude them from a bunch of UNION operations, wrap the UNION group in () as a subquery:

SELECT email FROM (

SELECT cand_email AS email FROM tbl_cand_data

UNION

SELECT emp_email AS email FROM tbl_emp_data

UNION

SELECT email FROM AS email tbl_uptade_list

UNION

SELECT feed_email AS email FROM tbl_feedback

UNION

SELECT admin_email AS email FROM tbl_admin_emails

) email_list

WHERE email NOT IN (SELECT un_email FROM tbl_unsubscribe)