Isuru Isuru - 6 months ago 8
SQL Question

Subtract two result sets SQL

Result1 Result2
a a
b b
a


How to obtain
a
by doing a subtraction between these two result sets.
NOT EXISTS
and
NOT IN
return nothing for these two result sets. But i want
a
to be returned. Pleas help!

Answer

Hmmm . . . This is tricky because you want to take counts into account. One method would be:

select r1.col, r1.cnt - coalesce(r2.cnt, 0)
from (select col, count(*) as cnt
      from result1
      group by col
     ) r1 left join
     (select col, count(*) as cnt
      from resuult2
      group by col
    ) r2
    on r1.col = r2.col
where r1.cnt > coalesce(r2.cnt, 0);

This doesn't return exactly what you want, but it might suffice. Another method would use row_number():

select r1.col
from (select col, row_number() over (partition by col order by col) as seqnum
      from result1
      group by col
     ) r1 left join
     (select col, row_number() over (partition by col order by col) as seqnum
      from resuult2
      group by col
    ) r2
    on r1.col = r2.col and r1.seqnum = r2.seqnum
where r2.col is null;