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, 4

T2: 2, 3, 4, 4, 4, 5

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