sql - 找到第一个结果时短路 UNION 查询

Question

我构建了一个非常基本的 UNION 查询，以便确定传递到我的查询中的 UUID 的“类型”，如下所示:

(
  SELECT
  CASE WHEN id IS NOT NULL THEN 'player_id' ELSE '' END AS uuid_type 
  FROM db.players
  WHERE id = $1
)
UNION
(
  SELECT
  CASE WHEN id IS NOT NULL THEN 'game_id' ELSE '' END AS uuid_type 
  FROM db.games
  WHERE id = $1
)
UNION
(
  SELECT
  CASE WHEN id IS NOT NULL THEN 'location_id' ELSE '' END AS uuid_type 
  FROM db.locations
  WHERE id = $1
)
UNION
(
  SELECT
  CASE WHEN id IS NOT NULL THEN 'promo_id' ELSE '' END AS uuid_type 
  FROM db.promos
  WHERE id = $1
)

有没有办法“短路”这个查询，以便在找到结果时停止。例如，如果第一个子查询成功并且 uuid_type 设置为 player_id 我希望查询停止，因为现在不需要检查其他三个表。

Answer 1

我没有可用的 Postgres 来测试它。但它应该像这样工作:

SELECT t.uuid_type
FROM (
    (
      SELECT
      CASE WHEN id IS NOT NULL THEN 'player_id' ELSE '' END AS uuid_type 
      FROM db.players
      WHERE id = $1
    )
    UNION ALL
    (
      SELECT
      CASE WHEN id IS NOT NULL THEN 'game_id' ELSE '' END AS uuid_type 
      FROM db.games
      WHERE id = $1
    )
    UNION ALL
    (
      SELECT
      CASE WHEN id IS NOT NULL THEN 'location_id' ELSE '' END AS uuid_type 
      FROM db.locations
      WHERE id = $1
    )
    UNION ALL
    (
      SELECT
      CASE WHEN id IS NOT NULL THEN 'promo_id' ELSE '' END AS uuid_type 
      FROM db.promos
      WHERE id = $1
    )
) t LIMIT 1;

LIMIT 1 将结果限制为单行。通过将 UNION 替换为 UNION ALL，查询应该会更高效，因为它不再需要识别和删除重复项。