[ACCEPTED]-Minus operator giving me erros in mysql-mysql

Accepted answer
Score: 18

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.

See examples and performance comparisons here

Score: 3

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)
      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