Short-circuit a UNION query when first result found

Question

I have built a very basic UNION query in order to determine the 'type' of a UUID passed into my query like so:

(
  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
)

Is there a way to 'short-circuit' this query so that it stops when a result is found. For example, if the first sub-query succeeds and uuid_type is set to player_id I would like the query to stop, as checking the other three tables is now unnecessary.

Answer 1

I don't have Postgres available to test it. But it should work like this:

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 limits the result to a single row. And by replacing UNION with UNION ALL, the query should be more efficient as well as it doesn't need to identify and remove duplicates anymore.