unionAll

Syntax

unionAll(tableA, tableB, [byColName=false])

or

unionAll(tables, [partition=true], [byColName=false])

Arguments

For the first scenario, tableA and tableB are 2 tables with the same number of columns.

For the second scenario, tables is a list of tables with the same number of columns; partition is an optional Boolean parameter with the default value of true.

byColName is a Boolean value indicating whether the table combination is conducted along columns with the same name. If byColName =false, the table combination is conducted based on the order of columns regardless of column names.

Details

For the first scenario, combine 2 tables into a single table. The result is an unpartitioned in-memory table.

For the second scenario, combine multiple tables into a single table. If partitioned is set to “false”, the result is an unpartitioned in-memory table; if partitioned is set to “true”, the result is a partitioned in-memory table with sequential domain. The default value is “true”.

If byColName =false, all tables to be combined must have identical number of columns.

If byColName =true, the tables to be combined can have different number of columns. If a column does not exist in a table, it is filled with NULL values in the final result.

Examples

Example 1

$ t1=table(1 2 3 as id, 11 12 13 as x)
$ t2=table(4 5 6 as id, 14 15 16 as x)
$ re=unionAll(t1,t2)
$ re;

id	x
1	11
2	12
3	13
4	14
5	15
6	16

$ typestr(re);
IN-MEMORY TABLE

Example 2

$ t1=table(1 2 3 as id, 11 12 13 as x)
$ t2=table(4 5 6 as id, 14 15 16 as x)
$ t3=table(7 8 as id, 17 18 as x)
$ re=unionAll([t1,t2,t3])
$ select * from re;

id	x
1	11
2	12
3	13
4	14
5	15
6	16
7	17
8	18

$ typestr(re);
SEGMENTED IN-MEMORY TABLE

Example 3. About byColName

$ t1=table(1 2 3 as id, 11 12 13 as x)
$ t2=table(14 15 16 as x, 4 5 6 as id)
$ unionAll(t1,t2,true);

id	x
1	11
2	12
3	13
4	14
5	15
6	16

$ t1=table(1 2 3 as id, 11 12 13 as x)
$ t2=table(14 15 16 as x, 4 5 6 as id)
$ unionAll(t1,t2);

id	x
1	11
2	12
3	13
14	4
15	5
16	6

From the examples above, please make sure column names and their order are identical in all tables to be combined if byColName is not specified (i.e., byColName =false).

$ t1=table(1 2 3 as id, 11 12 13 as x, 21 22 23 as y)
$ t2=table(4 5 6 as id, 14 15 16 as x)
$ unionAll(t1,t2,true);

id	x	y
1	11	21
2	12	22
3	13	23
4	14
5	15
6	16

$ t1=table(1 2 3 as id, 11 12 13 as x, 21 22 23 as y)
$ t2=table(4 5 6 as id, 14 15 16 as x)
$ unionAll(t1, t2) => The number of columns of the table to insert must be the same as that of the original table.

From the examples above, if the tables to be combined have different number of columns, we must set byColName =true.