does minus always automatically return distinct ? 1

[Advocate]

a colleague's tests appear to prove that the results of an oracle minus operation are always distinct

but i cannot find anything in the official oracle sql references (surprise surprise !!!)

i have found other references which indicate that minus is a synonym for iso-sql except distinct (which i am not familiar with).

if it is true then its another little gem that i was blissfully ignorant of ... we live and learn !

could anyone confirm/deny/argue/comment please ?


regards, david - no such thing as problems - only solutions.

 

[SantaMufasa]

David,

SantaMufasa's First Law of Computer Heuristics:

One test is worth ten expert opinions.

SantaMufasa's Corollary to the First Law of Computer Heuristics:

One test trumps the documentation.

Given this Law and Corollary, don't be afraid to try it out for your answer. Following are my findings:

select * from a;

         A
----------
         1
         1
         1
         2
         2
         3
         3
         3

select * from b;

         B
----------
         2
         2

select * from a minus select * from b;

         A
----------
         1
         3

So, this little test proves your hypothesis that a MINUS produces a distinct result set.

Additionally, UNION produces a distinct, sorted result set, but UNION ALL does not do the sorting and the distinct.

Let us know if this information resolves your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[Advocate]

Mufasa,

correct - empirical testing seemed to indicate that this was the case but I was surprised that there does not seem to be an explicit mention of it in any officvial oracle sql documenmtation that i can find

as a mentor, i wanted to ensure staff were aware of this and hopefully point them at an authoritative oracle reference - i can always try otn

curious, were you aware of this ? tell the truth now ...


regards, david - no such thing as problems - only solutions.

 

[SantaMufasa]

Actually, David, I taught for Oracle Education for 63 dog years, and yes, was aware of this, but not because I was smart...it was because I had been bitten by this undocumented behaviour in front of a class of 24. You tend not to forget lessons learned by that method.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[Advocate]

Mufasa,

I used to train as well and always dreaded the student's devastatingly simple question ...

i also found that you dont really understand a subject until you have to teach it

one dog year = how many man years ?

by the way, currently in sydney but hoping to be closer to you in a year or so ... herself is beavering away for her usa nursing exams ... might get a chance to buy you a guinness


regards, david - no such thing as problems - only solutions.

 

[SantaMufasa]

Dave,

We'd love to host you if you can get close to the 2002 Olympics venue.

We measure tenure at Oracle in dog years because one year at Oracle is like seven years anywhere else.

Cheers, mate.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.