Caveman's Blog

My commitment to learning.

Sql Server: Union vs Union All

leave a comment »


This post is to showcase the subtle yet important difference between the usage of Union and Union All in SQL.The output of query when Union of two or more select statements returns the distinct rows returned by all the select queries, where as when the Union All is used, all the rows returned by each select query will be in the grand output.

Let me demonstrate with an example:

declare @table1 Table(Id int identity(1,1), name varchar(50))
declare @table2 Table(Id int identity(1,1), name varchar(50))

--Insert dummy date into table 1
Insert @table1(name) values('test 1')
Insert @table1(name) values('test 2')
Insert @table1(name) values('test 3')

--Insert dummy date into table 2
Insert @table2(name) values('test 1')
Insert @table2(name) values('test 2')
Insert @table2(name) values('test 3')

--UNION: returns 3 rows
select * from @table1
union
select * from @table2

--UNION ALL: returns 6 rows
select * from @table1
union all
select * from @table2

and here is the output:

union

Advertisements

Written by cavemansblog

June 13, 2009 at 10:10 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: