[ACCEPTED]-Minus operator giving me erros in mysql-mysql
MySQL does not support
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