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 are views in sql?

0 Answers  

What is integrity constraints?

5 Answers   Ramco, Satyam,

What is difference between stored function and application function?

0 Answers  

How will you make performance better for application including front end and back

2 Answers   Metric Stream,

What is audit logout in sql profiler?

0 Answers  

Is big data nosql?

0 Answers  

What is the purpose of a sql?

0 Answers  

In a package if we have 10 procedures or functions,How to know which will execute first?

1 Answers   Sollet Soft,

Hi, I am new in oracle(SQL), could anyone help me in writing a correct SQL. Below is the table structure. Table: Subsc Fields: 1. Sub_no (this field will hold values of subscriber nos, for e.g. S111111, S222222, S333333, S444444, etc.) 2. s_status (this field will hold values for different status of subscriber, for e.g. 'A', 'S', 'C', etc.) 3. cus_id (this field will hold values of bill nos for e.g. 11111111, 22222222, 33333333, 44444444, etc.) Table: Bill Fields: 1. Bill_no this field will hold values of bill nos for e.g. 11111111, 22222222, 33333333, 44444444, etc.) 2. b_status = (this field will hold values for different status of bill for e.g. 'O', 'C', 'S', etc.) Note: 1. The Sub_no is a Primary key of Subsc table. 2. The cus_id is a foreign in Subsc table (referred from Bill_no field of Bill table) 3. The Bill_no field is the Primary key of Bill table. Query A --> I wrote a query to select cus_id/Bill_no which is in status open (b_status = 'O') and having more than two active subscriber (i.e. S_status = 'A') in it ( i.e. more the two subscribers in same bill). select s.cus_id from subsc s where exists (select 1 from bill where bill_no = s.cus_id and b_status = 'O') and s_status = 'A' group by s.cus_id having count(sub_no) = 2 Problem : The above query will give the cus_id (or rather bill_no) which are in open status (b_status ='O) and which are having TWO ACTIVE Subscribers (s_status ='A') in it. However, this query will also lists the cus_id/bill_no which are having more than TWO subscribers in it (but only two subscriber will be in Active status (s_status = 'A') and the others will be in s_status = 'C' or s_status = 'S'. Help needed: I want to write a query which will fetch ONLY the cus_id/bill_no which are in open status (b_status ='O') and which are having ONLY TWO ACTIVE subscribers (s_status ='A') in it. B--> If I include the sub_no in the above query then NO row are returned. select s.cus_id, s.sub_no from subsc s where exists (select 1 from bill where bill_no = s.cus_id and b_status = 'O') and s_status = 'A' group by s.cus_id, s.sub_no having count(sub_no) = 2 Help needed: I want to modify the above query which will fetch ONLY the cus_id/bill_no which are in open status (b_status ='O') and which are having ONLY TWO ACTIVE subscribers (s_status ='A') in it ALONG with the sub_no. Thanks a lot in advance. Regards, Nitin

0 Answers  

How to avoid using cursors? What to use instead of cursor and in what cases to do so?

0 Answers  

What is difference between cursor and trigger?

0 Answers  

How many clustered indexes can be created on a table?

0 Answers