Re: Except (Minus) all and Intersect all
- From: David Aldridge <david@xxxxxxxxxxxxxxxxxx>
- To: gints.plivna@xxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 16 May 2008 09:19:36 -0700 (PDT)
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@xxxxxxxxx>
To: oracle-l <oracle-l@xxxxxxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
