能否用IN子句将值与多列比较？SQL查询写法咨询

原查询语句 #

select m.value
from MY_TABLE m
where m.value in (select m2.some_third_value, m2.some_fourth_value
                  from MY_TABLE_2 m2
                  where m2.first_val member of v_my_array
                  or m2.second_val member of v_my_array_2)

问题说明 #

是否可以编写类似查询，让m.value与两列比较且至少匹配其中一列？比如写成where m.value in (select m2.first_val, m2.second_val)的形式，还是必须编写两个独立的查询？

解答 #

首先，where m.value in (select m2.first_val, m2.second_val)这种写法不可行。因为IN子查询返回的是包含两列的行集合，而m.value是单个字段值，数据结构不匹配，数据库会直接抛出错误。

不需要拆分两个独立查询，有以下几种简洁的实现方式：

方式1：用UNION合并子查询结果

通过UNION将两列的符合条件的值合并成单个结果集，再用IN匹配：

select m.value
from MY_TABLE m
where m.value in (
    -- 取符合第一个数组条件的first_val
    select m2.first_val from MY_TABLE_2 m2
    where m2.first_val member of v_my_array
    union
    -- 取符合第二个数组条件的second_val
    select m2.second_val from MY_TABLE_2 m2
    where m2.second_val member of v_my_array_2
)

• 若不需要去重，用UNION ALL替代UNION，性能更优；
• UNION会自动去重，适合需要避免重复匹配的场景。

方式2：用OR连接两个IN条件

直接拆分两个独立的IN子查询，用OR逻辑连接：

select m.value
from MY_TABLE m
where m.value in (select m2.first_val from MY_TABLE_2 m2 where m2.first_val member of v_my_array)
   or m.value in (select m2.second_val from MY_TABLE_2 m2 where m2.second_val member of v_my_array_2)

这种写法逻辑直观，容易理解，适合简单场景。

方式3：用EXISTS子查询匹配

通过EXISTS判断是否存在满足任一匹配条件的行：

select m.value
from MY_TABLE m
where exists (
    select 1 from MY_TABLE_2 m2
    where (m2.first_val member of v_my_array and m2.first_val = m.value)
       or (m2.second_val member of v_my_array_2 and m2.second_val = m.value)
)

这种写法在数据量较大时，有时能获得更好的性能，因为数据库可以提前终止匹配判断。

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