.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Union vs. Union All : Practical View

Posted By:Gaurav Arora       Posted Date: April 29, 2009    Points: 25    Category: DataBase    URL: http://www.dotnetspark.com  
 

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

 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend