Answer / suresh

union is used to select distinct values from two tables
where as union all is used to select all values including
duplicates from the tables

Answer / a.jyothsna

Suresh,
you are exactly correct

Answer / ajith

A UNION statement eliminates duplicate rows;
A UNION ALL statement includes duplicate rows.

Answer / gaurav arora

The answer-1 is very much correct. Let me clear it with
example:

Union vs. Union All
In simple we can say that
1. union is used to select distinct values from two
tables,where as union all is used to select all values
including duplicates from the tables.
2. The UNION operator allows you to combine the results of
two or more SELECT statements into a single result set. The
result sets combined using UNION must all have the same
structure. They must have the same number of columns, and
the corresponding result set columns must have compatible
data types.By default, the UNION operator removes duplicate
rows from the result set. If you use UNION ALL, all rows
are included in the results and duplicates are not removed.

Lets consider following examples:

1. UNION
Select * from dbo.checkDuplicate
Union --it will leave the duplicate rows
Select * from dbo.checkDuplicate

The above querry will retrieve all rows from checkduplicate
table except duplicate entries.

2. UNION ALL
Select * from dbo.checkDuplicate
Union --it will select all rows including duplicates
Select * from dbo.checkDuplicate

The above querry will select all rows from checkduplicate
table including duplicate entries.

Note: One can count the number of rows using following
statement:

SELECT rows FROM sysindexes WHERE id = OBJECT_ID
('checkDuplicate') AND indid < 2

To get above used table please run following querry:
CREATE TABLE dbo.checkDuplicate
(
[ID] [int] ,
[FirstName] [varchar](25),
[LastName] [varchar](25)
) ON [PRIMARY]

INSERT INTO dbo.checkDuplicate VALUES(1, 'Gaurav','Arora')
INSERT INTO dbo.checkDuplicate VALUES(2, 'Shuby','Arora')
INSERT INTO dbo.checkDuplicate VALUES(3, 'Amit','Gupta')
INSERT INTO dbo.checkDuplicate VALUES(1, 'Gaurav','Arora')
INSERT INTO dbo.checkDuplicate VALUES
(5, 'Neelima','Malhotra')
INSERT INTO dbo.checkDuplicate VALUES(4, 'Shweta','Arora')
INSERT INTO dbo.checkDuplicate VALUES(4, 'Shweta','Arora')
INSERT INTO dbo.checkDuplicate VALUES(2, 'Meghna','Arora')

Thanks,
Gaurav Arora
http://stuff4mdesktop.blogspot.com/

Answer / praveen k. agrawal

union is used For select distinct values from two tables
where as union all is used For select all values including
duplicates from the tables.

For Example Table X contain values(a,b,c) and Table Y
contain values(c,d,e)
In Case Of Union we will get Result-a,b,c,d,e
and other hand (Union All ) we will get-a,b,c,c,d,e.

Answer / sugumar.p

Suresh is perfectly correct

Answer / banti

Jyothsna,

you are correct too.

Regards,
Bunty

Answer / 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.

Answer / anjani kumar

Union will return only distinct values from two tables.
Union all will return duplicate values from the two tables

Answer / ranjay kumar singh

union is used to select distinct values from two tables
where as union all is used to select all values including
duplicates from the tables

what is msql? : Sql dba

0 Answers  

what is the functionality of the function htmlentities? : Sql dba

0 Answers  

Why do we use procedures?

0 Answers  

How to find string or key value using pl/sql code?

2 Answers   Ramco,

How to update salary of employees department wise?

6 Answers  

What is identity column in sql server?

0 Answers  

What is secondary key?

0 Answers  

What is multiple columns?

0 Answers  

What type of database is sql?

0 Answers  

what are pl/sql collections?

4 Answers   JPMorgan Chase, Oracle,

What are analytic functions in sql?

0 Answers  

What is mutating table error?

0 Answers