Saturday, July 12, 2008

What is difference between Union and Union All?

I have a got a comment and a question from an anonymous for my post How to Insert multiple rows?. I must say thanks to you all to encourage my stuffs.

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

3 comments:

  1. hi gaurav a very good article the same i have recommended to my team kee it up

    ReplyDelete
  2. i got many things in a single roof its a very good blog i appreciate the way you are writting

    ReplyDelete
  3. Thanks Namarata and Suman!

    I will try my level best to post more stuffs.

    ReplyDelete