SQL查询实现多表指定ID存在性校验并输出布尔结果矩阵

多ID查询方案（支持批量输入） #

写法1（适配支持VALUES子句的数据库：PostgreSQL/MySQL 8.0+/SQL Server等） #

SELECT
  input_id,
  EXISTS(SELECT 1 FROM table_1 WHERE id = input_id) AS table_1_exists,
  EXISTS(SELECT 1 FROM table_2 WHERE id = input_id) AS table_2_exists,
  EXISTS(SELECT 1 FROM table_3 WHERE id = input_id) AS table_3_exists,
  -- 剩余47张表按照上面的格式依次补充即可
  -- 可选全表存在标记列
  (
    EXISTS(SELECT 1 FROM table_1 WHERE id = input_id)
    AND EXISTS(SELECT 1 FROM table_2 WHERE id = input_id)
    AND EXISTS(SELECT 1 FROM table_3 WHERE id = input_id)
    -- 剩余47张表的EXISTS判断也依次补充到这里
  ) AS exists_in_all
FROM (
  -- 替换为你的输入ID列表
  VALUES (111), (222), (333), (444), (123), (456)
) AS t(input_id);

仅需替换VALUES中的ID列表、补全剩余表的判断逻辑即可直接运行，只要每个表的id字段建有索引，50张表的查询性能也可以保证。

写法2（兼容低版本不支持VALUES派生表的数据库，如MySQL 5.x） #

SELECT
  input_id,
  EXISTS(SELECT 1 FROM table_1 WHERE id = input_id) AS table_1_exists,
  EXISTS(SELECT 1 FROM table_2 WHERE id = input_id) AS table_2_exists,
  EXISTS(SELECT 1 FROM table_3 WHERE id = input_id) AS table_3_exists,
  (EXISTS(SELECT 1 FROM table_1 WHERE id = input_id) AND EXISTS(SELECT 1 FROM table_2 WHERE id = input_id) AND EXISTS(SELECT 1 FROM table_3 WHERE id = input_id)) AS exists_in_all
FROM (
  SELECT 111 AS input_id
  UNION ALL SELECT 222
  UNION ALL SELECT 333
  UNION ALL SELECT 444
  UNION ALL SELECT 123
  UNION ALL SELECT 456
) AS t;

单ID查询方案 #

SELECT
  123 AS input_id,
  EXISTS(SELECT 1 FROM table_1 WHERE id = 123) AS table_1_exists,
  EXISTS(SELECT 1 FROM table_2 WHERE id = 123) AS table_2_exists,
  EXISTS(SELECT 1 FROM table_3 WHERE id = 123) AS table_3_exists,
  (EXISTS(SELECT 1 FROM table_1 WHERE id = 123) AND EXISTS(SELECT 1 FROM table_2 WHERE id = 123) AND EXISTS(SELECT 1 FROM table_3 WHERE id = 123)) AS exists_in_all;

将代码中的123替换为目标查询ID、补全剩余表的判断逻辑即可。

兼容返回格式说明 #

如果你的数据库执行后返回的是1/0而非布尔值，可以用CASE WHEN转换为字符串形式的TRUE/FALSE，示例：

CASE WHEN EXISTS(SELECT 1 FROM table_1 WHERE id = input_id) THEN 'TRUE' ELSE 'FALSE' END AS table_1_exists

内容的提问来源于stack exchange，提问作者ECLIPSE-MAN