What does UNION do? What is the difference between UNION and UNION ALL?

Question

What is the difference between Joiner and Union Transformation? Also, Should we use Router instead of Joiner, to increase performance when there are multiple sources?

Answer 1

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.

Answer 2

Hello, there is slight difference between this two function ...

UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not remove duplicate record.

There is a performance hit when using UNION instead of UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).

here example is,
UNION:

SELECT 'foo' AS bar UNION SELECT 'foo' AS bar

Result:

+-----+
| bar |
+-----+
| foo |
+-----+
1 row in set (0.00 sec)

UNION ALL

SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar

Result:

+-----+
| bar |
+-----+
| foo |
| foo |
+-----+
2 rows in set (0.00 sec)