Difference between union and union all.

Question

Kindly provide me along with example

Answer 1

Check the below links:

http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimal-performance-comparison/

http://sqlserverplanet.com/tsql/the-difference-between-union-and-union-all

Answer 2

Union ALL preserves duplicates , whereas UNION doesn't( So Union is more costly as it has to sort and get rid of duplicates)

declare @tab1 table(id int);
insert into @tab1 values
(1),(2),(3),(4),(5)	

declare @tab2 table(id int);
insert into @tab2 values
(4),(5),(6),(7),(8)

select * from @tab1
union 	
select * from @tab2

select * from @tab1
union All
select * from @tab2

---

Satheesh
My Blog

Answer 3

sandip,

UNION provides the distinct set of results, while UNION ALL provides the entire result set.

If you are sure that the result sets that you are combining will be distinct by themselves, you can go for UNION ALL in place of UNION - as its better performance-wise.

--Union
select 1 as id
UNION 
select 1
UNION 
select 2

--Union all
select 1 as id
UNION ALL
select 1
UNION ALL
select 2

---

Thanks,
Jay
<If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>