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 view means?

0 Answers  

what is oracle database ? : Sql dba

0 Answers  

What are the operators used in select statements?

0 Answers  

Why self join is used in sql?

0 Answers  

What is the difference between having and a where in sql?

0 Answers  

what are the drivers in mysql? : Sql dba

0 Answers  

Can function return multiple values in sql?

0 Answers  

What is rank function in sql?

0 Answers  

what is the difference between mysql_fetch_array and mysql_fetch_object? : Sql dba

0 Answers  

What is a recursive stored procedure?

0 Answers  

How to raise user-defined exception with custom sqlerrm ?

0 Answers  

what is a foreign key ? : Sql dba

0 Answers