Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

SQL Server - Difference between Union and Union All

Jul 23, 2014
Introduction:

Here I will explain difference between UNION and UNION ALL in SQL serverUnion operator will return unique records from tables and Union All in SQL server will return all the records from the tables including duplicate values also.

Description:

In previous articles I explained SQL Server change identity column value,  replace multiple spaces with single space in sql, reseed identity column value in sql server, get only month and year from date in sql server, substring function in SQL server and many articles relating to SQL server. Now I will explain difference between UNION and UNION ALL in SQL server.

Generally Union Operators are used to combine the result of two or more select queries into single result set.

SQL UNION Operator:

SQL Union Operator is used to combine the result of two or more select statement queries into single result set. The Union Operator is used to select only distinct values from two tables.

SQL Union Operator Syntax:

SELECT column1,column2 FROM table1
UNION
SELECT column1,column2 FROM table2
Here one more thing we need to remember that is we can use Union Operator for the tables which is having same column names and same data types otherwise it will throw error like this

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
Now I will explain with one example first design two tables in your tables like this

UserInfo

UserID
UserName
Location
     1
Suresh
Hyderabad
     2
Prasanthi
Hyderabad
     3
Mahesh
Vizag

After that create another table and give name as UserDetails

UserID
UserName
Location
     1
Suresh
Hyderabad
     2
Nagaraju
Bangalore
     3
Madhav
Nagpur

Now write the Union Operator Query to get all the user details from two tables like this

SELECT UserName,Location FROM UserInfo
UNION
SELECT UserName,Location FROM UserDetails
Resultant table will be like this
UserName
Location
Suresh
Hyderabad
Prasanthi
Hyderabad
Mahesh
Vizag
Nagaraju
Bangalore
Madhav
Nagpur

If you observe above resultant table it contains UserDetails with distinct records because Union Operator will return only distinct records. If we want all the records then we need to use UNION ALL Operator.

SQL UNION ALL Operator:

This operator is used in a situation like return all the records from the tables including duplicate values also.  

SQL UNION ALL Operator Syntax:

SELECT column1,column2 FROM table1
UNION ALL
SELECT column1,column2 FROM table2
Result table

UserName
Location
Suresh
Hyderabad
Suresh
Hyderabad
Prasanthi
Hyderabad
Mahesh
Vizag
Nagaraju
Bangalore
Madhav
Nagpur

The main difference between Union and Union ALL operator is

Union operator will return distinct values but Union ALL returns all the values including duplicate values.

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

7 comments :

Anonymous said...

Good and Simple one

Hareesh said...

Nice, Simply superb....

Unknown said...

very simple & easy to understand..thank you!!!

Unknown said...

Thank you for posting this article,easy to understand..keep it up!!!

Mohsin Azam said...

Suresh!i need login page and signup page with code behind and sql server tables immeditely !Can YOU?
thanks in advance..
email:mohsin.afridi91@gmail.com

REENA SHARMA said...

Nice post thanks Suresh.
I need a help from you can you please send me the code for private tab in web browser like firefox.
Thanks in advance.:)

Unknown said...

Very Easy to Understand sir..... Keep it up

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.