ARRAY_EXCEPT
更新时间：2025-10-17
描述
返回一个数组，包含所有在 array1 内但不在 array2 内的元素，不包含重复项，如果输入参数为 NULL，则返回 NULL
语法
SQL
1ARRAY_EXCEPT(<arr1> , <arr2> )参数
| Parameter | Description |
|---|---|
<arr1> |
源数组 |
<arr2> |
需要与 arr1 比较的元素数组 |
返回值
返回一个数组，特殊情况：
- 如果输入参数为 NULL，则返回 NULL
举例
SQL
1CREATE TABLE array_type_table (
2 k1 INT,
3 k2 ARRAY<INT>,
4 k3 ARRAY<INT>
5)
6duplicate key (k1)
7distributed by hash(k1) buckets 1
8properties(
9 'replication_num' = '1'
10);
11INSERT INTO array_type_table VALUES
12(1, [1, 2, 3], [2, 4, 5]),
13(2, [2, 3], [1, 5]),
14(3, [1, 1, 1], [2, 2, 2]);
15select k1,k2,k3,array_except(k2,k3) from array_type_table;
Text
1+------+-----------------+--------------+--------------------------+
2| k1 | k2 | k3 | array_except(`k2`, `k3`) |
3+------+-----------------+--------------+--------------------------+
4| 1 | [1, 2, 3] | [2, 4, 5] | [1, 3] |
5| 2 | [2, 3] | [1, 5] | [2, 3] |
6| 3 | [1, 1, 1] | [2, 2, 2] | [1] |
7+------+-----------------+--------------+--------------------------+
SQL
1CREATE TABLE array_type_table_nullable (
2 k1 INT,
3 k2 ARRAY<INT>,
4 k3 ARRAY<INT>
5)
6duplicate key (k1)
7distributed by hash(k1) buckets 1
8properties(
9 'replication_num' = '1'
10);
11INSERT INTO array_type_table_nullable VALUES
12(1, [1, NULL, 3], [1, 3, 5]),
13(2, [NULL, NULL, 2], [2, NULL, 4]),
14(3, NULL, [1, 2, 3]);
15select k1,k2,k3,array_except(k2,k3) from array_type_table_nullable;
Text
1+------+-----------------+--------------+--------------------------+
2| k1 | k2 | k3 | array_except(`k2`, `k3`) |
3+------+-----------------+--------------+--------------------------+
4| 1 | [1, NULL, 3] | [1, 3, 5] | [NULL] |
5| 2 | [NULL, NULL, 2] | [2, NULL, 4] | [] |
6| 3 | NULL | [1, 2, 3] | NULL |
7+------+-----------------+--------------+--------------------------+
SQL
1CREATE TABLE array_type_table_varchar (
2 k1 INT,
3 k2 ARRAY<VARCHAR>,
4 k3 ARRAY<VARCHAR>
5)
6 duplicate key (k1)
7distributed by hash(k1) buckets 1
8properties(
9 'replication_num' = '1'
10);
11INSERT INTO array_type_table_varchar VALUES
12(1, ['hello', 'world', 'c++'], ['I', 'am', 'c++']),
13(2, ['a1', 'equals', 'b1'], ['a2', 'equals', 'b2']),
14(3, ['hasnull', NULL, 'value'], ['nohasnull', 'nonull', 'value']),
15(3, ['hasnull', NULL, 'value'], ['hasnull', NULL, 'value']);
16select k1,k2,k3,array_except(k2,k3) from array_type_table_varchar;
Text
1+------+----------------------------+----------------------------------+----------------------+
2| k1 | k2 | k3 | array_except(k2, k3) |
3+------+----------------------------+----------------------------------+----------------------+
4| 1 | ["hello", "world", "c++"] | ["I", "am", "c++"] | ["hello", "world"] |
5| 2 | ["a1", "equals", "b1"] | ["a2", "equals", "b2"] | ["a1", "b1"] |
6| 3 | ["hasnull", null, "value"] | ["hasnull", null, "value"] | [] |
7| 3 | ["hasnull", null, "value"] | ["nohasnull", "nonull", "value"] | ["hasnull", null] |
8+------+----------------------------+----------------------------------+----------------------+
SQL
1CREATE TABLE array_type_table_decimal (
2 k1 INT,
3 k2 ARRAY<DECIMAL(10, 2)>,
4 k3 ARRAY<DECIMAL(10, 2)>
5)
6duplicate key (k1)
7distributed by hash(k1) buckets 1
8properties(
9 'replication_num' = '1'
10);
11INSERT INTO array_type_table_decimal VALUES
12(1, [1.1, 2.1, 3.44], [2.1, 3.4, 5.4]),
13(2, [NULL, 2, 5], [NULL, NULL, 5.4]),
14(1, [1, NULL, 2, 5], [1, 3.1, 5.4]);
15select k1,k2,k3,array_except(k2,k3) from array_type_table_decimal;
Text
1+------+--------------------------+--------------------+----------------------+
2| k1 | k2 | k3 | array_except(k2, k3) |
3+------+--------------------------+--------------------+----------------------+
4| 1 | [1.00, null, 2.00, 5.00] | [1.00, 3.10, 5.40] | [null, 2.00, 5.00] |
5| 1 | [1.10, 2.10, 3.44] | [2.10, 3.40, 5.40] | [1.10, 3.44] |
6| 2 | [null, 2.00, 5.00] | [null, null, 5.40] | [2.00, 5.00] |
7+------+--------------------------+--------------------+----------------------+