What is the difference between UNION and UNION ALL?

What is the difference between union and union all in sql server? Is this related to joins(I can specify full join). Thank you all.

more ▼

asked May 01, 2010 at 03:29 PM in Default

avatar image

51 1 2 6

(comments are locked)
10|1200 characters needed characters left

9 answers: sort voted first

I find the best way of explaining this is in terms of the execution plan.

If you look at the operator that is used to combine the two sets for a UNION ALL, you'll see it's a Concatenation operator, combining two sets of data without any further consideration of whether rows have already been seen, etc.

For a UNION, you can see a number of operations in play. You may see a Distinct Sort as it makes sure it doesn't return the same row twice. You may see a Full Outer Join, performed as either a Merge Join or a Hash Match. Or if each set involved in the UNION can be seen by the system as having no duplicates and no matches with the other set, you may find that it's performed in exactly the same way as a UNION ALL.

Almost every time you ask yourself what's different between two ways of writing a query, look at the differences in the execution plan - and remember that the differences you see are actually down to other aspects of the data.

Which one is better? Generally UNION ALL, but you need to focus on what your query needs to do and make sure you're asking the system the right question.

more ▼

answered May 02, 2010 at 12:48 AM

avatar image

Rob Farley
5.8k 16 22 28

(comments are locked)
10|1200 characters needed characters left

UNION is for joining 2 result sets, JOIN is for joining 2 tables, views, table-valued functions, table expressions and the like.

UNION ALL simply joins 2 result sets including all the rows. UNION joins 2 result sets and removes duplicate rows. UNION by itself incurs the overhead of a merge join so that it can remove these dupes. It also has a side affect of ordering the rows. If you do not care about dupes UNION ALL is more performant

more ▼

answered May 01, 2010 at 04:21 PM

avatar image

Scot Hauder
6.5k 13 16 22

I think you have a typo. UNION removes duplicates. UNION ALL does not remove duplicates.

May 01, 2010 at 07:35 PM TimothyAWiseman

exactly, thx

May 01, 2010 at 08:04 PM Scot Hauder

I think a key element is the final sentence - if you don't care about dups, then union all. Otherwise use the union.

May 01, 2010 at 08:32 PM CirqueDeSQLeil

Thank you all for nice explanation.can anybody give me simple example on this.consider both columns of the tables are of varchar datatype.in this case i want to how dupilcates removed in string concatenation.

May 02, 2010 at 12:44 PM sari
(comments are locked)
10|1200 characters needed characters left

Queue's answer covers the difference between UNION and UNION ALL well.

You should also be aware of EXCEPT and INTERSECT which are two further horizontal concatenation operators.

In terms of the difference between JOIN and UNION - think of one in terms of vertical concatenation, and think of the other in terms of horizontal concatenation. Each of them can return less rows than were present in the source (with the exception that join can repeat on the one side when there are multiples on the other), and each of them take the dimensions of your result set and extend them.

That's how I usually explain them, anyhow.

more ▼

answered May 01, 2010 at 05:23 PM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

Thank you sir.helpful to me a lot.

May 02, 2010 at 12:38 PM sari
(comments are locked)
10|1200 characters needed characters left

The main difference is UNION ALL will include all rows from the multiple recordsets being UNION'd together... Including any duplicates. BOL has a full description at http://msdn.microsoft.com/en-us/library/ms180026.aspx

more ▼

answered May 01, 2010 at 04:18 PM

avatar image

John Franklin
414 1 3 7

Nice and simple is good. +1

May 02, 2010 at 11:12 PM Jeff Moden
(comments are locked)
10|1200 characters needed characters left

We use union and union all to combine result sets which return the same number of columns with same data types. And no, it is not same as full outer join.

Lets take an example with one column.

Table1 Col1 1 2 3 4

Table2 Col2 3 4 5 6

Now, when I write


That would give me 1,2,3,4,5,6 - So it has removed the duplicates.

If I do

    SELECT Col1 FROM Table1
    SELECT Col2 FROM Table2

It would return me 1,2,3,4,3,4,5,6 - It has got the duplicates with it.

Hope this help.

more ▼

answered May 01, 2010 at 04:21 PM

avatar image

250 3 4 6

Remove the duplicates incurs overhead, so union all is generally more efficient.

May 01, 2010 at 07:36 PM TimothyAWiseman

I think it is important to mention that UNION removes duplicates from each input as well as from the output - so if Table1 contained ((1),(1),(1)) and Table2 contained ((2),(4),(4)) the output would be ((1),(2),(4)).

May 02, 2010 at 02:23 AM SQL Kiwi

hi Queue,can you give me simple example such that both columns of the table are varchar datatype(string)

May 02, 2010 at 12:35 PM sari
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 01, 2010 at 03:29 PM

Seen: 22079 times

Last Updated: Nov 19, 2013 at 12:26 PM

Copyright 2018 Redgate Software. Privacy Policy