| minus function [message #372800] |
Fri, 09 March 2001 13:39  |
Jay
Messages: 127
Registered: October 1999
|
Senior Member |
|
|
Does minus function gives you quique values?
Ex
select A,B from Table1
minus
select A,B from Table2
The results from the above query? Will they be unique?
|
|
|
|
| Re: minus function [message #372801 is a reply to message #372800] |
Fri, 09 March 2001 14:09  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
No, you need to add DISTINCT to the first query.
While you are about it, check the difference between UNION and UNION ALL. Union all is faster and may be more correct in many cases, it does not go through an extra stem to eliminate duplicates from the result set.
create table x (name varchar2(10), qty number);
create table y (name varchar2(10), qty number);
insert into x values ('bob', 1);
insert into x values ('ben', 1);
insert into y values ('bob', 1);
select name, sum(qty)
from (
select name, qty from x
union
select name, qty from y )
group by name
ben 1
bob 1 <== first result
select name, sum(qty)
from (
select name, qty from x
union ALL
select name, qty from y )
group by name
ben 1
bob 2 <== second result
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
