# Difference between union and union all.

• ### Question

• Kindly provide me along with example
Tuesday, February 11, 2014 8:25 AM

• 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

• Proposed as answer by Tuesday, February 11, 2014 9:20 AM
• Edited by Tuesday, February 11, 2014 9:20 AM
• Marked as answer by Monday, February 17, 2014 9:07 AM
Tuesday, February 11, 2014 9:13 AM
• Edited by Tuesday, February 11, 2014 8:31 AM
• Proposed as answer by Tuesday, February 11, 2014 9:19 AM
• Marked as answer by Monday, February 17, 2014 9:07 AM
Tuesday, February 11, 2014 8:29 AM

### All replies

• Edited by Tuesday, February 11, 2014 8:31 AM
• Proposed as answer by Tuesday, February 11, 2014 9:19 AM
• Marked as answer by Monday, February 17, 2014 9:07 AM
Tuesday, February 11, 2014 8:29 AM
• 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

• Proposed as answer by Tuesday, February 11, 2014 9:20 AM
• Edited by Tuesday, February 11, 2014 9:20 AM
• Marked as answer by Monday, February 17, 2014 9:07 AM
Tuesday, February 11, 2014 9:13 AM
• 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