Thread: Difference between union and union all

What is the difference between UNION and UNION ALL? Which one is preferred and why ?

Hi RSP,

UNION is used to select distinct values from two tables.

UNION ALL is used to select all values (including duplicates) from the tables.

Regards,
Karthi

Originally Posted by rsp2709

What is the difference between UNION and UNION ALL? Which one is preferred and why ?

Union selects only unique row (data) from all queries.
Union all selects all rows from all queries.

Example.

table1 has row (1,'Test')
table2 has row (1,'Test')

Union gives output
1,'Test'

Union all gives output
1,'Test'
1,'Test'

UNION removes duplicate rows from multiple queries.

UNION ALL shows all the rows from multiple queries.

As UNION removes duplicate rows, there is a sort involved, adding a
potentially big overhead to the process.

Hi RSP,

As in the above discussions

UNION is used to fetch the data from the two select queries with no duplication of data, where as UNION ALL fetches the data including the duplication.

UNION and UNION ALL are SET operators that are used for two different queries.

UNION will fetch the DISTINCT set of records returned by both the queries

UNION ALL will fetch all the records returned by both the queries.

The concept of UNION is similar to mathematics UNION Venn Diagrams (A U B)

Eg:

Code:

select * from emp
UNION
select * from emp1
/

select * from emp
UNION ALL
select * from emp1


In the above queries the number of columns and its datatypes should be same

union all is faster than union, as union involve sorting operation to find distinct records.

Very informative thread, this is one of popular interview question on SQL.