what is the difference between union and union all
- 14 Answers
- 42959 Views
- IBM, Luxoft, Oracle, I also Faced
- E-Mail Answers
Answer Posted / vishal
UNION removes duplicates, whereas UNION ALL does not.
In order to remove duplicates the result set must be
sorted, and this may have an impact on the performance of
the UNION, depending on the volume of data being sorted,
and the settings of various RDBMS parameters ( For Oracle
PGA_AGGREGATE_TARGET with WORKAREA_SIZE_POLICY=AUTO or
SORT_AREA_SIZE and SOR_AREA_RETAINED_SIZE if
WORKAREA_SIZE_POLICY=MANUAL ).
Basically, the sort is faster if it can be carried out in
memory, but the same caveat about the volume of data
applies.
Of course, if you need data returned without duplicates
then you must use UNION, depending on the source of your
data.
I would have commented on the first post to qualify the "is
much less performant" comment, but have insufficient
reputation (points) to do so.
| Is This Answer Correct ? | 9 Yes | 1 No |
Post New Answer View All Answers
What is before and after trigger?
What is embedded sql with example?
Which constraints we can use while creating database in sql?
How do I remove sql plus from windows 10?
Why we use triggers in mysql?
what are the disadvantages of mysql? : Sql dba
what are aggregate and scalar functions? : Sql dba
- Types of triggers - View - Dcl - Procedures, packages, functions - Metasolve - Can use Dcl in triggers - package case study - Cursor and its types - triggers schedule - Wrap - Why we are using fetch and for in cursor. difference?
What are the benefits of pl sql?
Write a sql query to get the third highest salary of an employee from employee_table?
What is the difference between row level and statement level trigger?
How global cursor can be declare with dynamic trigger ?
How can check sql version from command line?
What is number function in sql?
Why do we need a foreign key?
