UNION_ALL
Description
The UNION_ALL function returns the vertical concatenation of columns.
Warning
Operator performance This operator allows up to eight input arguments. Please note that an increasing number of provided arguments can have a more significant negative impact on filter propagation performance. We therefore recommend to use UNION_ALL on as few input columns as possible.
SYNTAX
UNION_ALL ( table.column1 , ... , table.columnN )
UNION_ALL requires at least 2 and at most 16 arguments.
NULL handling
NULL values are preserved.
Limitations
The current limitations documented in Engine Limitations also hold for the result of UNION_ALL
. This means that the hard upper limit of rows for the result column of UNION_ALL
is 2.1 billion rows, and the recommended limit of rows is 800 million rows.
The owning table of the input columns may not be temporary, i.e., constants and GLOBAL are not allowed.
Filter handling
Filters set on tables joined to the input tables to the UNION_ALL operator are projected to the input tables according to the standard filter propagation rules and then forwarded to the UNION_ALL table. In case the common table is not the UNION_ALL table but a table joined to it (like a domain table), the filter is further propagated according to the standard filter propagation rules.
Filters set on a UNION_ALL table are also projected back to the input tables. From there, the filters are further propagated to other connected tables. If a table is connected to multiple different input tables, the "distance" from it to the input tables determines how the projected UNION_ALL filters are propagated.
If a table t has the same distance from two (or more) different input tables, the (projected UNION_ALL) filters from each these tables are propagated to t and are or-ed together. If the distance is not the same, only the filters from the "closest" input tables are propagated. Filters from the input tables further away do not influence the shown values for t.
The "distance" is not directly the number of edges in the join graph, but the number of direction switches (from 1-to-n to n-to-1 and vice versa). In other words: longer runs of repeated 1-n joins only count as a distance of 1. The same holds for n-1 joins. So in the following example: "a 1-n t1 1-n t2 1-n t3 n-1 t4 n-1 t5 1-n b", the distance between the two tables a and b is 2 as there is a direction switch at t3 and at t5.
See below for an example of how this concretely works out in a simple example.
Please note that filter propagation through the Data Model might become less meaningful if input columns from many different tables are used inside UNION_ALL.
Nesting of UNION_ALL
UNION_ALL cannot be nested.
Examples
[1] Concatenation of two string columns | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[2] Filter on a UNION_ALL table affects the very same UNION_ALL table | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[3] We use the reverse order on the union tables to get another UNION table. The filters from the one table are propagated to the other table. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[4] Filters on a UNION_ALL table are propagated to their origin. From the origin tables the filters are propagated to the rest of the data model. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[5] If an origin table appears twice in the UNION_ALL table, one of the rows passing the filter on the UNION_ALL table is enough for it to be shown in the origin table. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[6] The next two examples (based on the same data model) show how the filter propagation works for tables that are connected to more than one UNION_ALL input table. For both examples the "activities" tables (on the n side) are connected by the same case table (on the 1 side) The table "activities2" is itself an origin table, so the filter that directly comes from the UNION_ALL (distance 0) dominates the filter that first has to be propagated from the "activities2" table (distance 1). | ||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||
|
[7] The next two examples (based on the same data model) show how the filter propagation works for tables that are connected to more than one UNION_ALL input table. For both examples the "activities" tables (on the n side) are connected by the same case table (on the 1 side) The table "activities2" is itself an origin table, so the filter that directly comes from the UNION_ALL (distance 0) dominates the filter that first has to be propagated from the "activities2" table (distance 1). | ||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||
|