|
In short, UNION does a sort-unique on both collections. UNION ALL just
retrieves all rows, regardless of any double values.
I came across a (similar) piece of code during my work at BT:
select distinct * from ...
union
select distinct * from ...;
It looks like there is a distinct done twice here.
To play around with this a bit, I created a simple table:
create table union_test ( value number );
Then I inserted a couple of record (with duplicate values):
insert into union_test(value) values (1);
insert into union_test(value) values (1);
insert into union_test(value) values (2);
insert into union_test(value) values (2);
insert into union_test(value) values (3);
insert into union_test(value) values (3);
insert into union_test(value) values (4);
insert into union_test(value) values (4);
insert into union_test(value) values (5);
insert into union_test(value) values (5);
Now I can run some queries to see the differences:
select * from union_test where value
The result for this query is as follows:
VALUE
----------
1
1
2
2
3
Note, the duplicate values. To get only unique values I need to add the
distinct operator.
You can read the rest of this article at http://blog.bar-solutions.com .php
|
Union vs Union All
File Attachments
User Comments 
Add a Comment
Related Article 
Language Translation
Search Knowledge Base