MySQL中的MINUS运算符? [英] MINUS operator in MySQL?

问题描述

我有一些桌子在那儿接收电子邮件.而且我不想在表tbl_unsubscribe中获取电子邮件.我写了这样的查询:

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    

但是我收到语法错误. MINUS运算符对MySQL无效吗?

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

推荐答案

在这里可以使用NOT IN()子查询，因为MySQL不支持MINUS.

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)

也可以用LEFT JOIN完成，在un_email列中查找NULL:

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

要将它们从一堆UNION操作中排除，请将UNION组包装在()中作为子查询:

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)

这篇关于MySQL中的MINUS运算符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！