[ACCEPTED]-Minus operator giving me erros in mysql-mysql
MySQL does not support EXCEPT or MINUS.
You can use 2 NOT EXISTS , OUTER JOIN ... NULL or NOT IN (be careful of NULLs) to do an anti 1 semi join.
Using a "not in" or "not exists" to perform 12 a "minus" query on very large data sets 11 can result in extremely long query times. I 10 came up with a method that mimics the set 9 based operations performed by other databases 8 (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 7 select yields very good performance on large 6 data sets vs the use of not exists or not 5 in. Essentially, it is looking for rows 4 that only exist in the first set and not 3 in the second. I have used this quite often 2 lately with very good success with respect 1 to performance.
More Related questions
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.