MattDionis MattDionis - 5 days ago 6
SQL Question

Short-circuit a UNION query when first result found

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

What about a coalesce?

Select coalesce((Select 'player_id' from db.players where id = $1), 
                (Select 'game_id' from db.players where id = $1), 
                (Select 'location_id' from db.players where id = $1), 
                (Select 'promo_id' from db.players where id = $1))
Comments