Oracle中高效检查Barcode值是否存在于多表的方法问询

当然可以用单条SQL实现，核心思路是通过EXISTS判断目标值是否在任意一张表中，这种方式比多次查询或统计总数更高效——只要找到匹配项就会停止后续检查，不用遍历所有数据。

方案1：用CASE返回1/0标识存在性 #

SELECT CASE 
         WHEN EXISTS (
             SELECT 1 FROM CONTAINER WHERE BARCODE = TO_CHAR(v_iTemp)
             UNION ALL
             SELECT 1 FROM TABLE2 WHERE BARCODE = TO_CHAR(v_iTemp)
             UNION ALL
             SELECT 1 FROM TABLE3 WHERE BARCODE = TO_CHAR(v_iTemp)
         ) THEN 1 
         ELSE 0 
       END INTO v_exists_flag
FROM DUAL;

方案2：用OR连接多个EXISTS（性能更优） #

这种写法效率更高，数据库只要在某一张表中找到匹配的barcode，就会终止其他表的查询：

SELECT 1 INTO v_exists_flag
FROM DUAL
WHERE EXISTS (SELECT 1 FROM CONTAINER WHERE BARCODE = TO_CHAR(v_iTemp))
   OR EXISTS (SELECT 1 FROM TABLE2 WHERE BARCODE = TO_CHAR(v_iTemp))
   OR EXISTS (SELECT 1 FROM TABLE3 WHERE BARCODE = TO_CHAR(v_iTemp))
UNION ALL
SELECT 0 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM CONTAINER WHERE BARCODE = TO_CHAR(v_iTemp))
  AND NOT EXISTS (SELECT 1 FROM TABLE2 WHERE BARCODE = TO_CHAR(v_iTemp))
  AND NOT EXISTS (SELECT 1 FROM TABLE3 WHERE BARCODE = TO_CHAR(v_iTemp));

若数据库支持布尔类型（如Oracle） #

可以直接返回布尔值：

SELECT CASE 
         WHEN EXISTS (SELECT 1 FROM CONTAINER WHERE BARCODE = TO_CHAR(v_iTemp))
              OR EXISTS (SELECT 1 FROM TABLE2 WHERE BARCODE = TO_CHAR(v_iTemp))
              OR EXISTS (SELECT 1 FROM TABLE3 WHERE BARCODE = TO_CHAR(v_iTemp))
         THEN TRUE 
         ELSE FALSE 
       END INTO v_exists_bool
FROM DUAL;

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