The keyword UNION is about forming the union of two tables. Figuratively speaking, we want to hang one table under the other one so that they become one long table. The prerequisite for this is, of course, that the two tables have the same number of columns.
The result in the example on the right could have been created with the following query:
SELECT docnr,
'INV' AS type,
amount,
curr,
customer
FROM invoice
UNION
SELECT docnr,
'ORD' AS type,
amount,
curr,
customer
FROM order;
The keyword UNION thus connects the result of two queries with each other. These involved queries do not always have to return one row. They can also be empty, return one line or any number of lines.
UNION and UNION ALL
Unfortunately, we could not see an important side effect of UNION in the example: it removes all duplicate lines. This is often not necessary, in the above example duplicates can even be excluded. This behavior of the UNION operator is due to the fact that it understands tables as sets in the mathematical sense. And in a set an element (=row) can be contained only once.
Therefore, as long as we know that there are no duplicate data in our tables, or if it doesn’t matter whether there are duplicate records in the result or not, we prefer to use the UNION ALL operator. This is considerably faster than UNION, since the effort of eliminating (supposed) duplicates is omitted. The difference in runtime is sometimes remarkable, depending on the volume of data.