15

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 ?

Nic Wortel
  • 11,155
  • 6
  • 60
  • 79
AssamGuy
  • 1,571
  • 10
  • 30
  • 43
  • possible duplicate of [MINUS in MySQL?](http://stackoverflow.com/questions/184592/minus-in-mysql) – Wooble Dec 05 '11 at 13:52
  • 1
    Would like to point out the SQL in the question as it stands is valid in MySQL. There is also no "MINUS" in the SQL, but the question is about MINUS (which MySQL doesnt support as discussed below) which is rather confusing ... – spinkus Oct 23 '13 at 12:55

2 Answers2

24

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)
Air
  • 8,274
  • 2
  • 53
  • 88
Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
  • @AssamGuy See addition above. – Michael Berkowski Dec 05 '11 at 14:03
  • It is showing `DB Error. Unknown column 'email' in 'field list' SELECT email FROM ( 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 ) email_list WHERE EMAIL NOT IN (SELECT un_email FROM tbl_unsubscribe) ` – AssamGuy Dec 05 '11 at 18:06
  • @AssamGuy Forgot the alias `AS email` in each UNION component. See change above. – Michael Berkowski Dec 05 '11 at 18:09
7

Unfortunately MINUS and INTERSECT are not supported by MySQL, but you can get the same result using JOIN for MINUS, UNION for INTERSECT.

SELECT cand_email FROM tbl_cand_data
LEFT JOIN tbl_unsubscribe ON (cand_email = un_email)
WHERE un_email IS NULL
Alessio Cantarella
  • 5,077
  • 3
  • 27
  • 34
Maxim Krizhanovsky
  • 26,265
  • 5
  • 59
  • 89