Except (Minus) all and Intersect all
Date: Fri, 16 May 2008 18:01:44 +0300
Reading the previous discussion about "deep copying a record" and suggestion publishing possibility on Oracle Mix, I've remembered of one possible enhancement I'd like to get for already some months. And particularly these are two missing set operators EXCEPT (MINUS) ALL and INTERSECT ALL. Just like there is UNION and UNION ALL actually SQL standard has ALL for another two ones. You of course know that UNION (without ALL), MINUS and INTERSECT are returning only unique values. But in case of ALL this is not true and I think can be quite handy in cases when there is necessity to compare and analyze two different data sets.
So lets imagine we have two sets:
T1: 1, 2, 2, 2, 3, 4, 4
T2: 2, 3, 4, 4, 4, 5
Then INTERSECT is 2, 3, 4
INTERSECT ALL would be 2, 3, 4, 4
T1 MINUS T2 is 1
T1 MINUS ALL T2 would be 1, 2, 2
I've suggested it in Oracle Mix here
https://mix.oracle.com/ideas/28856-add-except-minus-all-and-intersect-all-set-operators and if you feel that it could be nice to have you can browse there and press Want it.
I however don't know what influence on all that is in reality to Oracle, but hopefully someone will read that :)
Thank You! :)