What is the difference between UNION and UNION ALL

Question

What is the difference between UNION and UNION ALL?

Answer 1

UNION and UNION ALL used to combine two or more query results.

UNION command selects distinct and related information from two tables which will eliminates duplicate rows.

On the other hand, UNION ALL command selects all the values from both the tables, which displays all rows.

Answer 2

union is used to select distinct values from two tables where as union all is used to select all values including duplicates from the tables

Answer 3

Difference Between Union Vs Union ALL In Sql

What Is Union In SQL?

The UNION operator is used to combine the result-set of two or more data set .

Each SELECT statement within UNION must have the same number of columns
The columns must also have similar data types
The columns in each SELECT statement must also be in the same order

Union Vs Union All With Example

Answer 4

UNION merges the contents of two structurally-compatible tables into a single combined table.

• Difference:

The difference between UNION and UNION ALL is that UNION will omit duplicate records whereas UNION ALL will include duplicate records.

Union Result set is sorted in ascending order whereas UNION ALL Result set is not sorted

UNION performs a DISTINCT on its Result set so it will eliminate any duplicate rows. Whereas UNION ALL won't remove duplicates and therefore it is faster than UNION.*

Note: The performance of UNION ALL will typically be better than UNION, since UNION requires the server to do the additional work of removing any duplicates. So, in cases where it is certain that there will not be any duplicates, or where having duplicates is not a problem, use of UNION ALL would be recommended for performance reasons.

Answer 5

I add an example,

UNION, it is merging with distinct --> slower, because it need comparing (In Oracle SQL developer, choose query, press F10 to see cost analysis).

UNION ALL, it is merging without distinct --> faster.

SELECT to_date(sysdate, 'yyyy-mm-dd') FROM dual
UNION
SELECT to_date(sysdate, 'yyyy-mm-dd') FROM dual;

and

SELECT to_date(sysdate, 'yyyy-mm-dd') FROM dual
UNION ALL
SELECT to_date(sysdate, 'yyyy-mm-dd') FROM dual;