What is difference between Union and Union All?

Posted Date: 19 Jul 2008	Resource Type: Articles	Category: Databases
Author: Gaurav Arora	Member Level: Diamond
Rating:	Points: 10

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

For more details, visit http://stuff4mdesktop.blogspot.com/2008/07/what-is-difference-between-union-and.html

Responses

Author: sangeetha 29 Apr 2009

Member Level: Gold Points : 2

UNION

The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type.

Employees_Norway:

E_ID E_Name
01 Hansen, Ola
02 Svendson, Tove
03 Svendson, Stephen
04 Pettersen, Kari

Employees_USA:

E_ID E_Name
01 Turner, Sally
02 Kent, Clark
03 Svendson, Stephen
04 Scott, Stephen

List all different employee names in Norway and USA:

SELECT E_Name FROM Employees_Norway
UNION
SELECT E_Name FROM Employees_USA

Result:

E_Name

Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark

This command cannot be used to list all employees in Norway and USA. In the example above we have two employees with equal names, and only one of them is listed. The UNION command only selects distinct values.
Scott, Stephen

UNION ALL

The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

List all employees in Norway and USA:

SELECT E_Name FROM Employees_Norway
UNION ALL
SELECT E_Name FROM Employees_USA

Result

E_Name

Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Svendson, Stephen
Scott, Stephen

Author: D.Jeya kumar(JK) 29 Apr 2009

Member Level: Diamond Points : 1

Hi,

Good post. Nice article. keep posting more Good articles

Regards
JK

Author: mausumi 29 Apr 2009

Member Level: Gold Points : 1

Hi
Thanks for this article.
Keep continue postinting more articles

regards,
mausumi

Feedbacks

Popular Tags

What are tags ?

Search Tags

SqlServer . Interviews .

Post Feedback

This is a strictly moderated forum. Only approved messages will appear in the site. Please use 'Spell Check' in Google toolbar before you submit.
You must Sign In to post a response.

	Next Resource: T-SQL Guidelines Part 1
	Previous Resource: What is the result when comparing two nulls in SQL?
	Return to Discussion Resource Index
	Post New Resource
	Category: Databases

Post resources and earn money!

More Resources