UNION VS UNION ALL in Sql Server with Example

UNION and UNION ALL is the way to select records from different tables having the same number and type of columns. Union internally use DISTINCT to remove the duplicate records while Union All don't use distinct and shows all the records even duplicate records.

-- Table A               -- Table B        -- Table C
ID          VALUE        ID   VALUE        ID     VALUE
1            A           1      A           1      A
2            B           2      B           2      B
3            C           3      P           3      X

We will use above three tables A, B and C to understand union and union all.

Lets do the UNION with tables A and B to understand the UNION

SELECT ID, VALUE FROM A
UNION
SELECT ID, VALUE FROM B

As I said UNION internally use DISTINCT so result will have all the unique records, here is the output

ID       VALUE
1         A
2         B
3         C
3         P

Now we will see the UNION ALL with table A and B again

SELECT ID, VALUE FROM A
UNION ALL
SELECT ID, VALUE FROM B

As we know union all shows all the records, and never used distinct so here is the result

ID       VALUE
1          A
2          B
3          C
1          A
2          B
3          P

Let's see some unseen results, say you are doing UNION ALL between A and B and then UNION with table C like this

SELECT ID, VALUE FROM A
UNION ALL
SELECT ID, VALUE FROM B    
UNION 
SELECT ID, VALUE FROM C

So In this case A and B will select all the records and when it will do the UNION with C then all the records will be distinct, it is same as A union B union C, here is the result

ID          VALUE
----        ------
1           A
2           B
3           C
3           P
3           X

What would be the result if we will use A union B union all C?, A union B will give distinct records and then union all with table C, see the result

ID          VALUE
----        -------
1           A
2           B
3           C
3           P
1           A
2           B
3           X

Performance, which will perform better? Union All will always perform better than Union, when you're not concerned about eliminating duplicate records because you're avoiding an expensive distinct sort operation

Ali Adravi Having 13+ years of experience in Microsoft Technologies (C#, ASP.Net, MVC and SQL Server). Worked with Metaoption LLC, for more than 9 years and still with the same company. Always ready to learn new technologies and tricks.
  • union
  • union-all
  • sql server
By Ali Adravi On 14 Jun, 13  Viewed: 312

Other blogs you may like

Get precision and decimal value in SQL Server by using PARSENAME

In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty   On 26 Mar 2013  Viewed: 1,294

Available wildcards in sql server with examples

To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D   On 25 Feb 2013  Viewed: 357

Concatenate multiple columns in SQL Server with NULL value

When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi   On 13 Feb 2013  Viewed: 5,312

Bulk insert into SQL Server from CSV file by skipping first row

Bulk insert is used to Import a data file into a database table or view in a user-specified format. It's not daily using kind of code, so it's very common question on most of the site like. How to import CSV file into a table? How to insert records from a CSV file to a table, delimited by comma?... By Ali Adravi   On 08 Feb 2013  Viewed: 13,136

Error cannot be caught by a TRY…CATCH Construct in SQL Server

TRY…CATCH constructs do not trap the following conditions: - Warnings or informational messages that have a severity of 10 or lower. - Errors that have a severity of 20 or higher those stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of... By Ali Adravi   On 28 Dec 2012  Viewed: 372