array_intersect

SinceVersion 1.2.0

array_intersect

description

Syntax

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

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

notice

仅支持向量化引擎中使用

example

1. mysql> set enable_vectorized_engine=true;

3. mysql> select k1,k2,k3,array_intersect(k2,k3) from array_type_table;
4. +------+-----------------+--------------+-----------------------------+
5. | k1   | k2              | k3           | array_intersect(`k2`, `k3`) |
6. +------+-----------------+--------------+-----------------------------+
7. |    1 | [1, 2, 3]       | [2, 4, 5]    | [2]                         |
8. |    2 | [2, 3]          | [1, 5]       | []                          |
9. |    3 | [1, 1, 1]       | [2, 2, 2]    | []                          |
10. +------+-----------------+--------------+-----------------------------+

12. mysql> select k1,k2,k3,array_intersect(k2,k3) from array_type_table_nullable;
13. +------+-----------------+--------------+-----------------------------+
14. | k1   | k2              | k3           | array_intersect(`k2`, `k3`) |
15. +------+-----------------+--------------+-----------------------------+
16. |    1 | [1, NULL, 3]    | [1, 3, 5]    | [1, 3]                      |
17. |    2 | [NULL, NULL, 2] | [2, NULL, 4] | [NULL, 2]                   |
18. |    3 | NULL            | [1, 2, 3]    | NULL                        |
19. +------+-----------------+--------------+-----------------------------+

21. mysql> select k1,k2,k3,array_intersect(k2,k3) from array_type_table_varchar;
22. +------+----------------------------+----------------------------------+-----------------------------+
23. | k1   | k2                         | k3                               | array_intersect(`k2`, `k3`) |
24. +------+----------------------------+----------------------------------+-----------------------------+
25. |    1 | ['hello', 'world', 'c++']  | ['I', 'am', 'c++']               | ['c++']                     |
26. |    2 | ['a1', 'equals', 'b1']     | ['a2', 'equals', 'b2']           | ['equals']                  |
27. |    3 | ['hasnull', NULL, 'value'] | ['nohasnull', 'nonull', 'value'] | [NULL, 'value']             |
28. |    3 | ['hasnull', NULL, 'value'] | ['hasnull', NULL, 'value']       | ['hasnull', 'value']        |
29. +------+----------------------------+----------------------------------+-----------------------------+

31. mysql> select k1,k2,k3,array_intersect(k2,k3) from array_type_table_decimal;
32. +------+------------------+-------------------+-----------------------------+
33. | k1   | k2               | k3                | array_intersect(`k2`, `k3`) |
34. +------+------------------+-------------------+-----------------------------+
35. |    1 | [1.1, 2.1, 3.44] | [2.1, 3.4, 5.4]   | [2.1]                       |
36. |    2 | [NULL, 2, 5]     | [NULL, NULL, 5.4] | [NULL]                      |
37. |    3 | [1, NULL, 2, 5]  | [1, 3.1, 5.4]     | [1]                         |
38. +------+------------------+-------------------+-----------------------------+

keywords

ARRAY,INTERSECT,ARRAY_INTERSECT