array_union

array_union

description

Syntax

ARRAY<T> array_union(ARRAY<T> array1, ARRAY<T> array2)

返回一个数组，包含array1和array2的并集中的所有元素，不包含重复项，如果输入参数为NULL，则返回NULL

example

  1. mysql> select k1,k2,k3,array_union(k2,k3) from array_type_table;
  2. +------+-----------------+--------------+-------------------------+
  3. | k1   | k2              | k3           | array_union(`k2`, `k3`) |
  4. +------+-----------------+--------------+-------------------------+
  5. |    1 | [1, 2, 3]       | [2, 4, 5]    | [1, 2, 3, 4, 5]         |
  6. |    2 | [2, 3]          | [1, 5]       | [2, 3, 1, 5]            |
  7. |    3 | [1, 1, 1]       | [2, 2, 2]    | [1, 2]                  |
  8. +------+-----------------+--------------+-------------------------+
  10. mysql> select k1,k2,k3,array_union(k2,k3) from array_type_table_nullable;
  11. +------+-----------------+--------------+-------------------------+
  12. | k1   | k2              | k3           | array_union(`k2`, `k3`) |
  13. +------+-----------------+--------------+-------------------------+
  14. |    1 | [1, NULL, 3]    | [1, 3, 5]    | [1, NULL, 3, 5]         |
  15. |    2 | [NULL, NULL, 2] | [2, NULL, 4] | [NULL, 2, 4]            |
  16. |    3 | NULL            | [1, 2, 3]    | NULL                    |
  17. +------+-----------------+--------------+-------------------------+
  19. mysql> select k1,k2,k3,array_union(k2,k3) from array_type_table_varchar;
  20. +------+----------------------------+----------------------------------+---------------------------------------------------+
  21. | k1   | k2                         | k3                               | array_union(`k2`, `k3`)                           |
  22. +------+----------------------------+----------------------------------+---------------------------------------------------+
  23. |    1 | ['hello', 'world', 'c++']  | ['I', 'am', 'c++']               | ['hello', 'world', 'c++', 'I', 'am']              |
  24. |    2 | ['a1', 'equals', 'b1']     | ['a2', 'equals', 'b2']           | ['a1', 'equals', 'b1', 'a2', 'b2']                |
  25. |    3 | ['hasnull', NULL, 'value'] | ['nohasnull', 'nonull', 'value'] | ['hasnull', NULL, 'value', 'nohasnull', 'nonull'] |
  26. |    4 | ['hasnull', NULL, 'value'] | ['hasnull', NULL, 'value']       | ['hasnull', NULL, 'value']                        |
  27. +------+----------------------------+----------------------------------+---------------------------------------------------+
  29. mysql> select k1,k2,k3,array_union(k2,k3) from array_type_table_decimal;
  30. +------+------------------+-------------------+----------------------------+
  31. | k1   | k2               | k3                | array_union(`k2`, `k3`)    |
  32. +------+------------------+-------------------+----------------------------+
  33. |    1 | [1.1, 2.1, 3.44] | [2.1, 3.4, 5.4]   | [1.1, 2.1, 3.44, 3.4, 5.4] |
  34. |    2 | [NULL, 2, 5]     | [NULL, NULL, 5.4] | [NULL, 2, 5, 5.4]          |
  35. |    4 | [1, NULL, 2, 5]  | [1, 3.1, 5.4]     | [1, NULL, 2, 5, 3.1, 5.4]  |
  36. +------+------------------+-------------------+----------------------------+

keywords

ARRAY,UNION,ARRAY_UNION