Difference Between UNION and UNION ALL

What is the difference between union and union all in sql server?

The UNION operator joins result sets from tow or more SELECT statements. The basic Syntax of using “UNION” operator is

SELECT Column List FROM Table1

UNION [ALL]

SELECT Column List FROM Table2 

• ALL Clause in the query is optional

A couple of things that need to be kept in mind while using the “SQL UNION” operator are

1. The tables should have same data types for the columns
2. The Order in which we get the columns should be same
3. If we want to enforce Sort order for the final result set, we should have a “Order By” Clause on the last “SQL SELECT Statement” that we are going to have in our query.

One main difference between using the “ UNION ALL” clause and not using it is, when we use the “UNION” without the “ALL” Clause we get distinct records in the overall result set, but when we use the “UNION ALL” we get the complete set of results with out the removal of any duplicates.

So, based on this we can say that, UNION for SQL Server is just concatenation whereas UNION ALL has one more operation of identifying and removing the duplicates.

Now, let’s take an example to see what is happening???

Let’s go ahead and create two tables for our learning purpose, Table1 with a single int column and also table2 with single int column and insert some records with duplicates in them

Table 1

CREATE TABLE Table1(Col1 int)

INSERT INTO Table1 Values (1)

INSERT INTO Table1 Values (1)

INSERT INTO Table1 Values (2)

INSERT INTO Table1 Values (3)

INSERT INTO Table1 Values (3)

INSERT INTO Table1 Values (4)

Table 2

CREATE TABLE Table2(Col1 int)

 INSERT INTO Table2 Values (4)

INSERT INTO Table2 Values (5)

INSERT INTO Table2 Values (5)

INSERT INTO Table2 Values (6)

INSERT INTO Table2 Values (7)

INSERT INTO Table2 Values (8)

So, now let’s go and write the UNION queries on these tables and see what the result set is and also what is the Execution Plan for these queries to get better understanding of these operators.

SELECT Col1 FROM Table1

UNION

SELECT Col1 FROM Table2

SELECT Col1 FROM Table1

UNION ALL

SELECT Col1 FROM Table2

If you look at the operator that is used to unite the two sets for a UNION ALL, you’ll see it’s a Concatenation operator, combining two sets of data without any further thought of whether rows have previously been seen.

For a UNION, You can see a Distinct Sort as it makes sure it doesn’t return the same row twice.

So, now coming to the point on which one is better, from the above we can say that if duplicates are not an issue then definitely we should go for UNION ALL, but we need to really look at what our query needs to return and make sure users queries are answered correctly.

So, I think here I have pointed out nearly all the difference between union and union all in sql server.

If you have liked the article and want to get the articles (or) new posts at sql like, subscribe to articles through email by entering your email address in the top right and verifying the same. If you have any comments, then the below form is for you, go ahead!!!

Tagged as: SQL Queries, Syntax