What is the difference between UNION and UNION ALL

Question

What is the difference between UNION and UNION ALL?

Answer 1

The basic difference between UNION and UNION ALL is union operation eliminates the duplicated rows from the result set but union all returns all rows after joining.

from http://zengin.wordpress.com/2007/07/31/union-vs-union-all/

Answer 2

It is good to understand with a Venn diagramm.

here is the link to the source. There is a good description.

Answer 3

Suppose that you have two table Teacher & Student

Both have 4 Column with different Name like this

Teacher - ID(int), Name(varchar(50)), Address(varchar(50)), PositionID(varchar(50))

Student- ID(int), Name(varchar(50)), Email(varchar(50)), PositionID(int)

You can apply UNION or UNION ALL for those two table which have same number of columns. But they have different name or data type.

When you apply UNION operation on 2 tables, it neglects all duplicate entries(all columns value of row in a table is same of another table). Like this

SELECT * FROM Student
UNION
SELECT * FROM Teacher

the result will be

When you apply UNION ALL operation on 2 tables, it returns all entries with duplicate(if there is any difference between any column value of a row in 2 tables). Like this

SELECT * FROM Student
UNION ALL
SELECT * FROM Teacher

Output

Performance:

Obviously UNION ALL performance is better that UNION as they do additional task to remove the duplicate values. You can check that from Execution Estimated Time by press ctrl+L at MSSQL

Answer 4

If there is no ORDER BY, a UNION ALL may bring rows back as it goes, whereas a UNION would make you wait until the very end of the query before giving you the whole result set at once. This can make a difference in a time-out situation - a UNION ALL keeps the connection alive, as it were.

So if you have a time-out issue, and there's no sorting, and duplicates aren't an issue, UNION ALL may be rather helpful.

Answer 5

Not sure that it matters which database

UNION and UNION ALL should work on all SQL Servers.

You should avoid of unnecessary UNIONs they are huge performance leak. As a rule of thumb use UNION ALL if you are not sure which to use.

Answer 6

UNION
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

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.