Re: Except (Minus) all and Intersect all

I think that the fundamental difference between UNION and INTERSECT and MINUS is that the latter two can generally be performed with an outer join between the tables, which union cannot. INTERSECT ALL and MINUS ALL are simply outer joins with predicates to project the right rows, and INTERSECT and MINUS are the same with a distinct operator slapped on.

Maybe that is why UNION has an ALL option and the others do not.

• Original Message ---- From: Gints Plivna <gints.plivna_at_gmail.com> To: oracle-l <oracle-l_at_freelists.org> Sent: Friday, May 16, 2008 11:01:44 AM Subject: Except (Minus) all and Intersect all

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

Gints Plivna
http://www.gplivna.eu
--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Fri May 16 2008 - 11:19:36 CDT