如何将array_construct用于SQL的IN筛选条件？

错误原因 #

NOT IN 子句要求后面跟离散的取值列表，而 array_construct() 返回的是数组类型，两者类型不匹配，因此触发类型转换错误。

针对不同场景的解决方案 #

场景1：数组是固定值（如示例中的'A','B'）

直接去掉 array_construct()，把值直接作为离散列表传入 NOT IN：

select
    field_name,
    field_value
from 
    my_table
where field_name not in ('A', 'B')

场景2：数组来自另一列（动态取值）

使用数组专属的成员判断函数（以Snowflake为例，用ARRAY_CONTAINS），判断字段是否存在于目标数组中，再取反实现NOT IN的逻辑：

select
    t.field_name,
    t.field_value
from 
    my_table t
-- 替换成实际关联逻辑，关联存储数组的表
join other_table ot on t.id = ot.related_id
-- 将field_name转为数组元素匹配的类型（通常是VARIANT），再判断是否在数组中
where not ARRAY_CONTAINS(t.field_name::VARIANT, ot.target_array_column)

关键说明 #

• IN/NOT IN 仅适用于离散值列表，不能直接接收数组类型参数。
• 处理数组类型的成员判断时，必须使用对应数据库的数组函数（不同数据库函数名可能不同，比如PostgreSQL用<> ALL()或者NOT EXISTS配合UNNEST）。

内容的提问来源于stack exchange，提问作者cvluepke