What is the difference between UNION and UNION ALL

Question

What is the difference between UNION and UNION ALL?

Answer 1

(From Microsoft SQL Server Book Online)

UNION [ALL]

Specifies that multiple result sets are to be combined and returned as a single result set.

ALL

Incorporates all rows into the results. This includes duplicates. If not specified, duplicate rows are removed.

UNION will take too long as a duplicate rows finding like DISTINCT is applied on the results.

SELECT * FROM Table1
UNION
SELECT * FROM Table2

is equivalent of:

SELECT DISTINCT * FROM (
    SELECT * FROM Table1
    UNION ALL
    SELECT * FROM Table2) DT

---

A side effect of applying DISTINCT over results is a sorting operation on results.

UNION ALL results will be shown as arbitrary order on results But UNION results will be shown as ORDER BY 1, 2, 3, ..., n (n = column number of Tables) applied on results. You can see this side effect when you don't have any duplicate row.

Answer 2

You can avoid duplicates and still run much faster than UNION DISTINCT (which is actually same as UNION) by running query like this:

SELECT * FROM mytable WHERE a=X UNION ALL SELECT * FROM mytable WHERE b=Y AND a!=X

Notice the AND a!=X part. This is much faster then UNION.

Answer 3

One more thing i would like to add-

Union:- Result set is sorted in ascending order.

Union All:- Result set is not sorted. two Query output just gets appended.

Answer 4

In very simple words the difference between UNION and UNION ALL is that UNION will omit duplicate records whereas UNION ALL will include duplicate records.

Answer 5

UNION - results in distinct records

while

UNION ALL - results in all the records including duplicates.

Both are blocking operators and hence I personally prefer using JOINS over Blocking Operators(UNION, INTERSECT, UNION ALL etc. ) anytime.

To illustrate why Union operation performs poorly in comparison to Union All checkout the following example.

CREATE TABLE #T1 (data VARCHAR(10))

INSERT INTO #T1
SELECT 'abc'
UNION ALL
SELECT 'bcd'
UNION ALL
SELECT 'cde'
UNION ALL
SELECT 'def'
UNION ALL
SELECT 'efg'


CREATE TABLE #T2 (data VARCHAR(10))

INSERT INTO #T2
SELECT 'abc'
UNION ALL
SELECT 'cde'
UNION ALL
SELECT 'efg'

Following are results of UNION ALL and UNION operations.

A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.

Using UNION results in Distinct Sort operations in the Execution Plan. Proof to prove this statement is shown below:

Answer 6

UNION removes duplicate records in other hand UNION ALL does not. But one need to check the bulk of data that is going to be processed and the column and data type must be same.

since union internally uses "distinct" behavior to select the rows hence it is more costly in terms of time and performance. like

select project_id from t_project
union
select project_id from t_project_contact  

this gives me 2020 records

on other hand

select project_id from t_project
union all
select project_id from t_project_contact

gives me more than 17402 rows

on precedence perspective both has same precedence.