Both UNION and UNION ALL is used to combine results of two
SELECT queries, main difference between them is that UNION doesn't include
duplicate record but UNION ALL does.
Another difference between them is that UNION ALL is faster
than UNION but may look slow because it returns more data.
UNION is very
different than other SQL commands because it operates on data rather than
columns. Anyway, answer of this question is simple, though both UNION and UNION
ALL are used to combine result of two separate SQL queries on same or different
table, UNION does not keep duplicate
record (a row is considered duplicate if value of all columns are same), while
UNION ALL does. Since you mostly don't want duplicate rows, UNION is preferred over
UNION ALL in reporting and application development. User should keep in mind
that UNION ALL performance better than UNION because it doesn't have to remove
duplicate. This keyword is supported by all major database e.g. Oracle,
Microsoft SQL Server, MySQL etc.
Difference between UNION and UNION ALL command in SQL
1) Both UNION and UNION ALL are used to combine result of
two separate SQL query, it could be on same table or different table but data
should be same.
2) Key difference between UNION and UNION ALL is that former
will remove duplicates but later will keep them. In another words, UNION is
equal to running distinct on output of UNION ALL.
3) Due to above difference query execution time of UNION ALL
is smaller than UNION, which means former runs faster than later. So if you
want faster output and don't care of duplicates use UNION ALL.
4) Keep in mind that benefits gained by not removing
duplicates can be easily wiped out by transferring more data over a poor
bandwidth network connection. That's why in practice some time UNION ALL appear
slower than UNION because it return lot of data with duplicates which require
more time to travel from database server to client machine. So evaluate
performance of UNION and UNION ALL case by case.
5) Another worth noting thing while using UNION and UNION
ALL is that all queries combined using a UNION, INTERSECT or EXCEPT operator
must have an equal number of expressions in their target lists. For example if
result of query 1 has three column and result of query 2 has two column then
you cannot combine them using UNION command.
