SQL优化：如何高效检查指定值是否存在于多字段中？

当需要判断多个字段是否包含指定值列表，且字段/值列表较长时，你可以用以下几种更简洁易维护的方式，同时兼顾效率：

1. 利用数组交集（PostgreSQL专属） #

PostgreSQL支持数组操作，可以把字段和目标值都转成数组，用交集运算符&&判断是否有重叠：

SELECT *
FROM TABLE
WHERE ARRAY[ACAD_PLAN_CD, ACAD_PLAN_CD_2, ACAD_PLAN_CD_3, ACAD_PLAN_CD_4, ACAD_PLAN_CD_5] 
      && ARRAY['PS_BS', 'PS_BA'];

如果要排除空值干扰，可以加过滤：

SELECT *
FROM TABLE
WHERE ARRAY_REMOVE(
          ARRAY[ACAD_PLAN_CD, ACAD_PLAN_CD_2, ACAD_PLAN_CD_3, ACAD_PLAN_CD_4, ACAD_PLAN_CD_5], 
          NULL
      ) && ARRAY['PS_BS', 'PS_BA'];

2. 列转行（UNPIVOT/UNION ALL） #

把多列转换成单行的多行数据，再和目标值匹配，这种写法在SQL Server、MySQL、Oracle等数据库都适用：

SQL Server 用UNPIVOT #

SELECT DISTINCT t.*
FROM TABLE t
UNPIVOT (
    plan_cd FOR plan_columns IN (
        ACAD_PLAN_CD, ACAD_PLAN_CD_2, ACAD_PLAN_CD_3, ACAD_PLAN_CD_4, ACAD_PLAN_CD_5
    )
) AS unpivoted_data
WHERE unpivoted_data.plan_cd IN ('PS_BS', 'PS_BA');

MySQL/Oracle 用UNION ALL #

SELECT DISTINCT t.*
FROM TABLE t
JOIN (
    -- 将多列转为单行的多个值
    SELECT ACAD_PLAN_CD AS plan_cd FROM TABLE
    UNION ALL SELECT ACAD_PLAN_CD_2 FROM TABLE
    UNION ALL SELECT ACAD_PLAN_CD_3 FROM TABLE
    UNION ALL SELECT ACAD_PLAN_CD_4 FROM TABLE
    UNION ALL SELECT ACAD_PLAN_CD_5 FROM TABLE
) AS vals
ON vals.plan_cd IN ('PS_BS', 'PS_BA')
AND vals.plan_cd IS NOT NULL; -- 避免空值误匹配

3. 结合CTE管理目标值 #

如果目标值列表经常变化，可以用CTE（公共表表达式）存储目标值，再关联判断，写法更清晰：

WITH target_values AS (
    SELECT 'PS_BS' AS val UNION ALL 
    SELECT 'PS_BA' AS val
    -- 新增值直接加在这里
)
SELECT DISTINCT t.*
FROM TABLE t
JOIN target_values tv
ON tv.val IN (
    t.ACAD_PLAN_CD, t.ACAD_PLAN_CD_2, t.ACAD_PLAN_CD_3, t.ACAD_PLAN_CD_4, t.ACAD_PLAN_CD_5
);

效率说明 #

• 原有的OR + IN写法，如果字段有单独索引，数据库可能会利用索引做快速筛选，在数据量小时效率不错，但字段/值过多时代码冗余。
• 列转行的写法更易维护，但如果表数据量极大，可能需要全表扫描，此时可以考虑给转换后的列加索引，或者结合分区优化。

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