MINUS ALL and INTERSECT ALL in Oracle
Recently I was a bit studying SQL standard and a lot to my surprise found that there exists not only UNION ALL set operator, but also INTERSECT ALL and EXCEPT ALL (in Oracle version it would be MINUS ALL).
So you'd say Oracle haven't them and it is also (although partially - somehow minus has been forgotten) documented in Oracle Support for Optional Features of SQL/Foundation:2003.
Hmm, yea but a while ago I was doing several data migrations and then such set operators would really helped me. Now a bit what these non-existant (at least for Oracle) set operators are doing?
Imagine we have tables T1 and T2 with data as follows:
T1: 1, 2, 2, 2, 3, 4, 4T2: 2, 3, 4, 4, 4, 5So what is the result for SELECT * FROM T1 MINUS SELECT * FROM T2?
1
What would be the result for SELECT * FROM T1 MINUS ALL SELECT * FROM T2?
1, 2, 2
What is the result for SELECT * FROM T1 INTERSECT SELECT * FROM T2?
2, 3, 4
What would be the result for SELECT * FROM T1 INTERSECT ALL SELECT * FROM T2?
2, 3, 4, 4
As you can see ALL for EXCEPT/MINUS and INTERSECT retains cardinality and doesn't keep only unique records. Let's hope Oracle someday will stop this gap and give us this functionality :)
6 comments:
Well, the semantics of minus is non ambiguous. For intersection however, one have two options:
i. min, e.g
{2,2,2} min {2,2} = {2,2}
ii. product!
{2,2,2} min {2,2} = {2,2,2,2,2,2}
Given that intersection is a join, and join is a cartesian product, the second option is reasonable as well.
>Given that intersection is a
>join, and join is a cartesian
>product, the second option is
>reasonable as well.
Hmmm that's probably OK from some pure mathematician viewpoint but I'm always thought of intersection like operation that finds common elements among sets. So at least for me it is hard to imagine intersection as operation that can generate something more (i.e. more elements) than input sets contain. I imagine the classic Venn diagram of intersection (two partially overlapping circles) and it is hard for me to find out how intersection can be product of input elements.
Interesting link about similar topic:
How to simulate Minus/Intersect/Union All/Union set operation on characters in SQL
Well, intersection is a kind of join, so if you want this property to generalized smoothly to bags, you would expect intersection to give the same result as:
select a.x,b.x from (
select 1 x from dual
connect by rownum < 4
) a, (
select 1 x from dual
connect by rownum < 3
) b
where a.x=b.x
there is nothing like intersect all and minus all
Post a Comment