Minus operator giving me erros in mysql

Question

I have two queries.

First query returning 11 rows and second query returning 6 rows when i use the minus operator on them it should return 5 rows as far as my understanding

 SELECT location from uploads where username='Gates'
 MINUS
 SELECT fileshare FROM `whiteboard` where username='Gates' and friend='Curlyclouds'

But i am getting the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'minus SELECT fileshare FROM whiteboard where username='Gates' and friend='Cur' at line 2

Hope my question is clear and any help would be helpful to me .....Thank You

Answer 1

MySQL does not support EXCEPT or MINUS.

You can use NOT EXISTS , OUTER JOIN ... NULL or NOT IN (be careful of NULLs) to do an anti semi join.

See examples and performance comparisons here

Answer 2

Using a "not in" or "not exists" to perform a "minus" query on very large data sets can result in extremely long query times. I came up with a method that mimics the set based operations performed by other databases (merge, sort, remove duplicates).

select column1, count(*), min(setnum)
from
(
      select distinct column1, 1 as setnum
        from table1
   union all
      select distinct column1, 2 as setnum
        from table2
) as tbl_a
group by column1  
having count(*) = 1 and min(setnum) = 1

The above select yields very good performance on large data sets vs the use of not exists or not in. Essentially, it is looking for rows that only exist in the first set and not in the second. I have used this quite often lately with very good success with respect to performance.