Short-circuit UNION? (only execute 2nd clause if 1st clause has no results)

Question

If I do:

SELECT * FROM A   
WHERE conditions  
UNION  
SELECT * FROM B   
WHERE conditions 

I get the union of the resultset of query of A and resultset of query of B.

Is there a way/operator so that I can get a short-circuit OR result instead?

I.e. Get the result of SELECT * FROM A WHERE conditions and only if this returns nothing get the resultset of the SELECT * FROM B WHERE conditions ?

Answer 1

The short answer is no, but you can avoid the second query, but you must re-run the first:

SELECT * FROM A   
WHERE conditions  
UNION  
SELECT * FROM B   
WHERE NOT EXISTS (
    SELECT * FROM A   
    WHERE conditions)
AND conditions

This assumes the optimizer helps out and short circuits the second query because the result of the NOT EXISTS is false for all rows.

If the first query is much cheaper to run than the second, you would probably gain performance if the first row returned rows.

Answer 2

You can do this with a single SQL query as:

SELECT *
FROM A   
WHERE conditions  
UNION ALL
SELECT *
FROM B   
WHERE conditions and not exists (select * from A where conditions);