The UNION function returns a table that is the result of concatenating the rows of two or more tables.
UNION(
table1,
table2
[, table...]
)
- table: Reference to an existing table or expression that returns a table.
The UNION function returns a table containing all the rows of the tables being joined.
The tables to be joined must have the same number of columns. These are combined according to their position in the table, not according to their name.
The names of the columns of the table returned as a result of the function will be the same as those of the columns of the table passed as the first argument.
Duplicate rows will also appear duplicated in the result of the function. So if a row appears in both table1 and table2, it will be displayed twice in the result, and if it appears once in table1 and twice in table2, it will be displayed three times in the result.
The UNION function maintains the lineage of the columns only if the lineage of the two tables being joined is the same. If this is not the case, we will need to use the TREATAS function to assign the correct lineage to the generated table.
If we start from the following two tables, Countries1 and Countries2 :
...and we create a calculated table with the union of both:
Union = UNION(Countries1, Countries2)
...we can see that the result has 14 rows (8 from Countries1 table and 6 from Countries2 table) and contains duplicate rows. So, for example, the row "United Kingdom", 1 appears three times: two from the Countries1 table and one more from the Countries2 table.
If we have a third table, Countries3, with the same structure:
...we can calculate the union of the three tables with the following expression:
Union = UNION(Countries1, Countries2, Countries3)
Now suppose that the two tables to join are the already seen Countries1 and Countries2, but that the latter has País and Valor as field names:
The result of applying the same measure:
Union = UNION(Countries1, Countries2)
...is the concatenation of the rows, but receiving as column names those of the Countries1 table:
