Friday, May 16, 2008

Minus All and Intersect All

Today I rediscovered Oracle Mix ideas and remembered my surprise that there are also Minus all and Intersect all operators in SQL standard just like Union and Union all. I'll briefly remind You what exactly they were. Let's imagine we have two sets:

T1: 1, 2, 2, 2, 3, 4, 4
T2: 2, 3, 4, 4, 4, 5

Then T1 INTERSECT T2 is 2, 3, 4
T1 INTERSECT ALL T2 would be 2, 3, 4, 4

T1 MINUS T2 is 1
T1 MINUS ALL T2 would be 1, 2, 2

So by default Intersect and Minus have DISTINCT operator removing all non-unique values. But in case we add keyword ALL then all values and their cardinalities are counted as well. I think these operators could be quite handy in process of analysing two data sets and finding what exactly the difference is. So if You are thinking the same way then I kindly ask you to support my idea about intersect all and minus all with your voice in Oracle Mix.

Thank You in advance for any decision :) and I hope sooner or later (better sooner of course ;) to find that in New features guide.

No comments: